yogi_Computation Involving Multiple And Or Conditions

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

question is ...
my conditions sound like this:
IF D2 is >0 or D2 is <=100, then value is 0-100
IF D2 is >100 or D2 is <=500, then value is 100-500
IF D2 is >500 or D2 is <=1000, then value is 500-1000
IF D2 is >1000 or D2 is <=10000, then value is 1000-10000
IF D2 is >10000 or D2 is <=1000000, then value is 10000-1000000
----------------------------------------------------------------------------------

In the following solution, I have used the VLOOKUP function by setting up a table in cells A1:B7 ... when the LookUp value is entered in cell D2, the value is LookedUp in column A and the result from column B is put in cell E2.



The table in cells A1:B7 can be moved to another location in the same sheet or even to another sheet of the same spreadsheet.

yogi_ComputePayFromTimeSheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Chris1989 said:

How do I make my time sheet automatically calculate my total pay for the day. I have Time started in column C, Time stoped in column D, Total worked in column E, Pay rate in column F, and Daily total in column G. i am currently using formula  =ArrayFormula( IFERROR(D2:D - C2:C) ) in E2 and formula =ArrayFormula( IF(LEN(E2:E&F2:F) ; E2:E*F2:F ; IFERROR(1/0) ) ) in G2

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

Here we go Chris ...

yogi_TableGadget_SimpleExample

Google Spreadsheet

yogi_TableChartInteractive_SimpleExample

yogi_TableChartImage_SimpleExample

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Interactive Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_CreateUniqueIDfromTimeStampAndOtherColumns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Kurtworks said:
I would like Google Docs to generate a unique ID in the first column of my spreadsheet as soon as put data in that row. I would like the unique ID to take a number from the F column, the first letter of the first words from column's B and C and the timestamp the data was entered.


The Final unique ID would look like this 9KA1010201105034
9 being the number from column F
KA being the first letter of the first word in columns B and C
1010201105034 being the timestamp the data was entered (10/10/2011 05:34)


Is this possible to do? I have not clue how to script this so it there already a script out there that I could modify to do this? How would I do that?
---------------------------------------------------------------------------
In the following illustration I have created the UniqueID based on Kurtworks specification in column G just to the right of the ResponseTable in columns A:F

formula in cell G1 is ...
=ArrayFormula(if(row(G1:G)=1,"UniqueID",F1:F&B1:B&C1:C&substitute(text(A1:A,"mmddyyyyHH:mm"),":","")))
---------------------------------------------------------------------------
DarrenMWinter commented ...

I've been trying to adapt your formula here but am having some trouble with the time. I've adapted your formula to read:

=ArrayFormula(if(row(A1:A)=1,"UniqueID",C1:C&E1:E&substitute(text(F1:F,"mmdd-hh:mm"),":","")))

Where A is the row that the number appears in; and C and E are the reviewer's initials and date the comment was made.

The first result give is "m2dmw1024-1200". This is all right except for the time part. I don't understand why it reads 1200 for the time.

What I'd like to do is record something unique at the end of the ID number, like the time as hhmmss that the comment was entered - or even, as I have before in different software, make the unique part after the hyphen to read the number of seconds after midnight that the comment was created.
---------------------------------------------------------------------------
DarrenMWinter: 


in regard to your first comment, what is the time in the TimeStamp column? ... keep in mind that 


=text(value,"hh:mm") ... gives the hours in 12 hour clock format


=text(value,"HH:mm") ... gives the hours in 24 hour clock format