yogi_Create Computed Columns For Splitting Row By Row Multiple CheckBox Responses in A Single Cell Into Individual Columns

Google Spreadsheet   Post  #2045

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-29-2016

post by: Roscoe sprong:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Wd5MI2r20Eo;context-place=forum/docs
Split multiple responses into single column
Hi all

So I set up a Google Form and a few of the questions are multiple response, 

i.e. Q. Which of the following do you use, Answer = Product A, Product B, Product C..etc.

Now when forms exports the data to google sheets its lists the responses in one column

I.E Person A uses Product A, Product B, Product C

Person B uses  Product B, Product C

Person C uses Product A, Product C

Now I want to link which products are used to a different google sheet (completely new not a new page)

What function can i use so that it will find say just Product A and put it next to the persons name 

Name Email address        Product A     Product B     Product C

Ros    ros@google.com    Yes              Yes             No

I hope this makes sense and thanks in advance 

----------------------------------------------------------------------------------------------------

yogi_Setup A Time Schedule With 2 Times In The Same Row By Incrementing Times As Specified

yogi_Compute Row By Sum And Average Of Time Durations Given In Hours Minutes and Tenths Of Minutes

Google Spreadsheet   Post  #2043

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-24-2016

post by: Maddie7838:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/vl5I5yALwBI;context-place=mydiscussions
Average Durations
I have a series of durations in my data sheet and I am unable to average them. Every time I try to average I get the #DIV/0. How do I fix this?
---

Here's the link to the sample data that I'm trying to average: https://docs.google.com/spreadsheets/d/1VwWO4Qp52GvQOwUnhREt-EfgcFhTmo8S9Low1urvMVs/edit?usp=sharing .

I'm trying to average the times listed under Time 1, Time 2, and Time 3. I'd like them to appear in B3 (under average time), but I keep getting the following error: #DIV/0!. 

The answer should be 1:45.2. 

---------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Rows If Cells A11:D32 House An Entry Delineated In A Specified List E2:E

Google Spreadsheet   Post  #2042

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-21-2016

post by: hoolamonster:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/beCuzLLHj1s;context-place=mydiscussions
Highlight a row if a cell contains certain text.

in response to the comment by holymak...

In response to your query about an example.
Rather than a separate formula for each word ,I was after a single formula with multiple words:

E.g. Rather than =("rain"........)  would it be possible for

=("rain","United","wet"......)

Your formula works well but I have to write a conditional format for each word( which means hundreds of formulas).I want to be able to conditional formal rows that contain either of a number of words to be coloured a specific colour.
So if a row contains either " rain" and or "wet" and or "united" etc then that row would be a specific colour.

Any idea?

---------------------------------------------------------------------------------------------------------------------

FTR -- PlatForm For Reps To Log-In And Review Their Data

Fill-in the following Form to key-in your RepID and the Report you want to View

Please wait for your report to be generated and presented here on this page for your RepID

yogi_Pull Into Sheet2 Data From Table In Sheet1 By Matching Headers In Sheet2 With Those In Sheet1

Google Spreadsheet   Post  #2041

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-19-2016

post by: hoolamonster:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/tFirlL77KoE
Get Rows if Column Headers Match
I have two tabs, One is my master/control, the other tab needs to contain the same info minus some columns. (master has some sensitive info)

For the life of me I cant figure out a formula that basically does this:

If A2:BR2 (column header row) matches Master!A2:BR2 get rows/cells that belong to that column

I've been mixing different vlookups and filters and the combo of both in if statements, you name it, but I cant seem to get what i think should be easy, to work.

obviously I can write a query but I just hate listing the column letters out, it just gets long.

Any help would be awesome thank you

---

https://docs.google.com/spreadsheets/d/1JvU805Fu-8v4np81F-CFlPM5mWQYAZ4DjtfaoB1epe0/edit?usp=sharing

I would like to keep this entire thing dynamic, so if info changes on sheet1 sheet2 updates. and I would LOVE to do this with one formula, not pasting per column or using query and listing a million columns.

------------------------------------------------------------------------------------------------------------------

yogi_Compute How Many Dates In Column A Fall Within Last 7 Days 30 Days 90 Days

Google Spreadsheet   Post  #2040

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-19-2016

post by: Gary needs help please:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/Jjgbjtkw0XI
Counting a Date Range

Column A is the date

Column B returns the information

I want column B to tell me how many dates in Column A happened within the last week, month etc.

so for example:

20/02/2016

19/02/2016

10/02/2016

18/02/2016

11/02/2016

Column B should tell me 3 with the above example since 3 dates happened within the last 7 days.
----------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Rows If Columns A And Or B Contain The Word 'Rain'

Google Spreadsheet   Post  #2039

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-19-2016

post by: holymak:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/beCuzLLHj1s;context-place=forum/docs
Highlight a row if a cell contains certain text
Hoping someone can help please.

	A	B	C	D
1	The rain in Spain	Fifty Shades	2	3
2	Manchester united are struggling	Thats Life	5	6

So,for example I would like the whole of the row(A1:D1) to be high lighted if the text in column A or B contains "rain".

Is this possible ?

Grateful for any help.

-------------------------------------------------------------------------------------------------------------------------------

yogi_Given 'Never' Is 0 'Occasionally' is 1 'Often' is 2 'Very Often' is 3 Enumerate Text Entries In Column E

Google Spreadsheet   Post  #2038

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-17-2016

post by: Sub Help:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/R3Q0cGO2Y54;context-place=mydiscussions
Substituting Numbers for Text Using Arrary Formula

I am attempting to substitute numbers for text. However, two text response include the same word ("Often" and "Very Often"). I would like Sheets to substitute a "2" for "Often" and a "3" for "Very Often". 

I composed the following formula:

=ArrayFormula(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!AZ2:AZ10 , "Never" , 0) , "Occasionally" , 1) , "Often" , 2) , "Very Often", 3))

The formuala properly substitutes numbers for text with one exception. Instead of substituting a "3" for "Very Often", the output is "Very 2". 

Please help!

--------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Column By Column Cell With Maximum Value In The Corresponding Column

Google Spreadsheet   Post  #2037

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-15-2016

post by: Jibbons:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/8rlhKRCDfGE;context-place=forum/docs
Highlight MAX value in each column with single formula?

Hello! I would like to use conditional formatting to highlight the max value in a column. I know how to do this, but I have hundreds of columns I would like to highlight. Is there a custom formula that will find and highlight the max value in each column?

Thanks for any help!

yogi_Conditionally Format Cells A1:B1 -- Color Yellow if there is a match with entries in C:C otherwise Color Red

Google Spreadsheet   Post  #2036

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-13-2016

post by: Ethan Lloyd:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/EcDgP0mHd_Y;context-place=forum/docs
Conditionally Formatting based on the occurrence of multiple words

I have a cell with a day of the week in it. I want to check(to conditionally format) if that matches any of the days I have in a separate list.

Eg

Cell A1 - Tuesday

Cell B2 - Wednesday

List (C1:C3)

Monday 

Wednesday

Friday

So with this example, A1 should be a different colour to B1

------------------------------------------------------------------------------------------------

yogi_Facilitate Plotting Count Of Assigned Grades D C- C C+ B B- B B+ A- A A+ Left To Right From Lowest To Highest

Google Spreadsheet   Post  #2035

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-13-2016

post by: spore124:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/bEn6YoIs7iQ;context-place=forum/docs

Change order of bars on histogram of non-numeric data

How do I change the order of the bars on my histogram? It's counting student grades, C,C+,B-,B,B+ etc but it presents the bars in the wrong order. B+,C-,A,A+,B,A- etc. How do I manually set the order so that it increases in grade from left to right? 

yogi_Split A String Of Characters In Cells Of A Column With Each Character Going Into Its Own Cell In The Corresponding Row

Google Spreadsheet   Post  #2034

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-13-2016

post by: Galloping Penguin :

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/7f7lz7QJLpg;context-place=forum/docs
Split characters in string into individual columns

I have a range of strings which I  want to split so that each character from each string is put into its own column on the corresponding row

Example Strings

S6J1F267SD5E

EH2R4A5H58E2

G92T6FGN462B

 

Each string contains 12 characters, so for the first string I want to end up with S, 6, J, 1, F, 2, 6, 7, S, D, 5, E, each in their own column.

I tried using LEFT/RIGHT/MID but that ends up including extra characters which I don't want. And since each string contains random characters, I am not sure how setup a SPLIT, SEARCH, or REGEXTRACT formula to make them work.

yogi_Write A Formula To Find The Most Commonly Occuring Entry In A Column (be it numeric text symbol or mixed)

Google Spreadsheet   Post  #2033

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-08-2016

post by: terryallen123 :

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/COXNk2fxesA;context-place=forum/docs

A simple formula to find the most common text or number in a range

google sheets how to identify the most common number in a range of numbers and also the same for the most common text

yogi_Compute Mark Up On Wholesale Per Specified Criteria

Google Spreadsheet   Post  #2032

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-08-2016

post by: John Pitt :

https://productforums.google.com/forum/#!topic/docs/j_pOmrEg9-s;context-place=forum/docs

Setting up a price matrix for small business.

I have a set of wholesale cost ranges that I would like to reference. For example if the product cost is $1 to $15 the mark up is 300%  if the product cost is $15 to $45 the mark up is 250%  and so on.

I have a google docs sheet with my items name and description listed in column a and product cost in column b and would like to have the mark up automatically populate in column c.  Is this possible?

Any help will be appreciated.

--------------------------------------------------------------------------------------- 

yogi_Compute Time Duration A Person Signed Up For Given Data In HHMM-HHMM In The Same Day Or Spanning Overnight

Google Spreadsheet   Post  #2031

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-07-2016

post by: PAUL KOCH II :

https://productforums.google.com/forum/#!topic/docs/1nhiDcuvf4g;context-place=forum/docs
Getting the total hours from 2 times in the same cell

I have a spreadsheet that looks like this

RE/SQD Dr	Haggerty	1500-1900
RE/SQD Dr	Mark	1500-1900
RE/SQD Dr	Stefan	1500-1900
EMT	Rich W	1500-2000
EMT/Dr	scott	1530-2200
FF/EMT	Carlos	1500-1900

I want to take those times and figure out the total time they signed up for automatically. Can anyone help?
---------------------------------------------------------------------------------------------------------