Monday, February 29, 2016

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 
----------------------------------------------------------------------------------------------------


Wednesday, February 24, 2016

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. 
---------------------------------------------------------------------------------------------------------------------


Sunday, February 21, 2016

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

Friday, February 19, 2016

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.

ABCD
1The rain in SpainFifty Shades23
2Manchester united are strugglingThats Life56

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.
-------------------------------------------------------------------------------------------------------------------------------

Wednesday, February 17, 2016

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!
--------------------------------------------------------------------------------------------------------------------


Monday, February 15, 2016

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!

Sunday, February 14, 2016

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
------------------------------------------------------------------------------------------------

Saturday, February 13, 2016

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.

Thursday, February 11, 2016

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

Monday, February 8, 2016

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.



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

Sunday, February 7, 2016

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 DrHaggerty1500-1900
RE/SQD DrMark1500-1900
RE/SQD DrStefan1500-1900
EMTRich W1500-2000
EMT/Drscott1530-2200
FF/EMTCarlos1500-1900

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