Saturday, October 31, 2015

yogi_Convert Date and Time Text Entries In Column A To Date in YYYY-MM-DD 3 Char Day And Time In 24 Hour Format

Google Spreadsheet   Post  #1982
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-31-2015
post by  DevilMind:
https://productforums.google.com/forum/#!topic/docs/rp6oY6MnH2k;context-place=forum/docs
How to convert TEXT DATE to YYYY-MM-SS DAY HH:MM in Google Docs Spreadsheet ??
How to convert TEXT DATE to YYYY-MM-SS DAY HH:MM in Google Docs Spreadsheet ??
See image above for reference.
---------------------------------------------------------------------------------------------------------------------------


yogi_Compute Row By Sum Of Upto 5 Largest Scores From A Maximum Of Last 10 Scores

Google Spreadsheet   Post  #1981
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-31-2015
post by  Timothy Kerrigan:
https://productforums.google.com/forum/#!topic/docs/d_7RUaQb8Vw;context-place=forum/docs
Defining a dynamic range
I need help defining a range. The SS has 25 rows, and up to 55 columns of scores. There is a row for each individual, and their scores are recorded in columns by date. The individuals do not have scores in every column. I want to compute the average of the largest 5 of each individual's last 10 scores.

I am using the formula: =Average(Large(G5:Z5,1),Large(G5:Z5,2),Large(G5:Z5,3),Large(G5:Z5,4),Large(G5:Z5,5))

As I add scores, the range will change. It will be a lot of work to change them manually. I am looking for a way define a range from column G, to the column that has the column that has the 10th score, for each row.
------------------------------------------------------------------------------------------------------------------------------------
in the following solution I used the sample data from my fellow Top Contributor Chris Hicks -that he used while providing a response to question by Timothy - Thanks Chris for setting up the spreadsheet so nicely

Saturday, October 3, 2015

yogi_ComputeRolling Average Of Last Non-Blank 12 Entries In Range B4 to B

Google Spreadsheet   Post  #1979
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-03-2015
post by  Far Beyond Insanity:
https://productforums.google.com/forum/#!msg/docs/7H-fU0515zE/jb5a8TafBQAJ;context-place=topicsearchin/docs/category$3Aspreadsheets%7Csort:relevance%7Cspell:false
a rolling average question, need solution.
I am trying to get an average in a single column but cannot find any help here. All the suggestions I have found the formula errors out. I want to have this average automatically adjust to the last 12 fields that have been entered. Lets say at b1 I want the total of the last 12 fields in B. If it stats from b4 to b16 but when I add b17, b18, b19, and so on it will look at only the last 12. So when I get to b20, it is going to look at field b8 to b20 and average it. 

How do I make this happen?  
------------------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format First Instance Of Smallest 3 Values In Range C3 to P3

Google Spreadsheet   Post  #1978
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-03-2015
post by  Alpha Romero Championship:
https://productforums.google.com/forum/#!topic/docs/Q8hiaa0w_g0;context-place=topicsearchin/docs/category$3Aspreadsheets%7Csort:relevance%7Cspell:false
Smallest 3 values without duplicates
Hello,

I'm using three conditional formatting rules to find the smallest 3 values in a row and change the background to red.

C3:P3 equal to =SMALL($C$3:$P$3,1)
C3:P3 equal to =SMALL($C$3:$P$3,2)
C3:P3 equal to =SMALL($C$3:$P$3,3)

It works fine until you have a duplicate number.

So if the row had 1,2,3,4,5 the first three would highlight red.

If the row had 1,2,3,3,5 it would highlight 1&2 and both the 3s.

Is there a way to highlight only the first occurrence of a number if there are duplicates?

Thanks in advance
Matt
-----------------------------------------------------------------------------------------------------------------------------------------------