Cloud Computing -- Google Docs Way

Thursday, May 14, 2015

yogi_Rank Times That Include Fractions Of Seconds

Google Spreadsheet Post #1956
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 14, 2015

post by Thomas Reeve:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OyZoIP6LYiw
Exercise times in sheets

I'm helping a teacher with a spreadsheet that records rock climbing times. He wants to enter something like 3 minutes, 20.4 seconds into a cell and have it display as mm:ss.0. However, he also wants to rank the times (1st place with fastest time, etc), which means I need this stored as a TIME value. [I have set up a RANK function for him in another cell, but it does not work if the times are text values.]

I have taught him to enter his data as "00:03:20.4" which enters the data as a time value. However, when a time is entered into the cell, the display format always reverts back to "hh:mm:ss" (hiding the tenths of a second). He can "correct" the format by changing the column to the mm:ss.0 format, but this extra step seems to be confusing.

Is there a way to prevent the cell from reverting the format when I enter a time value?

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

Wednesday, May 13, 2015

yogi_Set Up QURY Formulation For Specified Criteria Involving Some Or All

Google Spreadsheet Post #1955
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 13, 2015

post by Shashi Krishna:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uXx8loY3tcQ

And/Or in an =QUERY condition question

Hey guys,

So I have a Sheet with 2 sheets in it. Sheet 1 has the following type of data:

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

NAME ABSENCE TYPE DATE FROM DATE TO

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

abc Sick Leave 01-05-2015 03-05-2015

def Personal Leave 02-04-2015 04-04-2015

abc Personal Leave 06-04-2015 07-04-2015

...

etc.

On Sheet 2 I have the following formula going.....

=query({importRange("documentID","Sheet1!A2:N")},"select Col1,Col2,Col3,Col5,Col6,Col7,Col8,Col13 where Col2 <> '' and (Col3="& "'" & H6 & "' or Col2="& "'" & I6 & "')and Col11='Approved' and toDate(Col5)>=date '"& TEXT( E6 , "yyyy-MM-dd" ) & "' and toDate(Col5) <= date '"& TEXT( G6 , "yyyy-MM-dd" ) & "' " , 0 )

H6 in Sheet2 is the Absence Type and I6 in Sheet2 is Name of the person.

How do I make the condition such that...

1. If name is provided then ALL absences of that person are shown.

2, If absence type AND name is provided then THAT SPECIFIC ABSENCE TYPE for THAT SPECIFIC USER is shown.

Since I am using an OR inside an AND...it works partially. Can someone please help?

Thanks.

Shashi

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

Monday, May 11, 2015

yogi_Plot For Times With Fractions Of Seconds

Google Spreadsheet Post #1955
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 12, 2015

post by Robin Scholten:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/YLJ5ji2MIgM
Add chart for 1500m times

I'm making a spreadsheet with result and on all of my performances I added a chart but for the 1500m it isn't working cause of google spreadsheets not recognizing time's above 1 min.

I changed the cell structure to time with s:mm,00 but it's not working.

Here's my spreadsheet with results and I would like to have the same type of chart in the 1500 like all my other disciplines.

https://docs.google.com/spreadsheets/d/18DozSNUpRIGjDvgbM7PMSHjnzsyjuuV23XSkraw95Bc/pubhtml

Hope you guys can help me.

Robin Scholten

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

yogi_Compute For DashBoard Number Of Unique Persons Who Visited Designated Cities In Specified Year

Google Spreadsheet Post #1954
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 11, 2015

post by angelbojangles:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/qqQl1bvOMiA
How to COUNTIF items in one column only once if there are duplicates in another column?

Sample dummy sheet here

Basically, my goal is to create an interactive graph that works off of countifs from Sheet1. You can see in Sheet1 there are six people (Abby, Bobby, Cindy, Danny, Eddy, and Frankie) who have visited Paris, New York, and London a number of times between 2007-2008.

I'm trying to figure out a countif formula for the interactive graph that counts how many INDIVIDUAL people have visited these cities (in 2007, 2008, or both). So even if Abby visited Paris in 2007 three times, she only gets counted once because she's only one person.

Does that make sense?

Thank you!

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

yogi_Compute Row By Row In Column G MilesDriven From Mileage Log Recorded In Column B Of 'Form Responses 1' Sheet

Google Spreadsheet Post #1953
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 11, 2015

post by mrludden:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/d3DXmC6EjTQ
Help with performing subraction between 2 adjacent values in the same column

here is my sheet. In column G of 'Form Responses 1' I am calculating the difference between the values in the same row of column B and the previous row of column B. However, the formula I am using also creates an entry on the next row of column G with a large negative number to deal with the last row. This is probably because I am not as familiar with the OFFSET formula I used to try and deal with the issue. My best solution at this point is to create a query() on a new blank sheet (which I've created but haven't done the query yet) and simply select the columns where column b is not null. Is this the best answer or is there a more efficient formula that will give me the difference between the adjacent rows without having this extra value? You will see I have the same issue in column J as well. Also, is IFERROR(IF()) less efficient than IF(ISBLANK()) ? I usually use the second version and I don't remember why I used the IFERROR instead this time. It seems that the arrayformulas take a little longer to populate on this particular sheet and I had a hunch that this was why so any info there is also much appreciated.

Thanks,

Mike

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

Sunday, May 3, 2015

yogi_Pull Latest Entry For Each Candidate For Each Day From Form_Responses Sheet (sorted in descending order by Timestamp column)

Google Spreadsheet Post #1952
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 03, 2015

post by Barb Reinan:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Hgm7WIc9wPI

Filter one response per day per person using time stamp and person's name

I am using this formula to filter responses from a form on another sheet within the same workbook. I am trying to pull one response per day per person, using the last response of the day. The formula works well for a few people and then breaks down, skipping people or dropping people from the sheet with the filtered responses. Does anyone know what is breaking?

=filter('Form Responses 4'!A:A, match('Form Responses 4'!A:A, query('Form Responses 4'!A:B, "select max(A) group by toDate(A), lower(B) label max(A) 'Timestamp'",1), 0))

Let me know if more information is necessary!

Barb

---

https://docs.google.com/spreadsheets/d/1csmW1xbgeS8UHV-cqlXlF43FYpgYhWZjPBMV5QGr4YE/edit?usp=sharing

Hope this helps!