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

yogi_CountCheckBoxResponsesFromFormSubmittals

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

kasumner said:
I am building a writing rubric form and corresponding spreadsheet. I am having trouble assigning a point value to each checked box to allow the total number of checked boxes for a section to render a total score on the spreadsheet. I have tried to use the "COUNTIF" formula but cannot figure out the correct syntax.
When I have 5 potential boxes for "organization" and a student scores in two areas, the two areas show in cell G2 of the spreadsheet. I would like to add a column next to it and have the program count the number of selections and assign a score. This is the formula I tried in cell H2 but it is not working. =COUNT(IF(ISTEXT(G2:text_A,text_B,text_C,text_D,text_E)))
This is the information in the cell which should have a value of two.
C. 4-6 complete sentences, D. 7 or more complete sentences
---------------------------------------------------------------------------------
In the ResponseTable in Google spreadsheet, the responses from CheckBoxes that are checked (tick marked) get all lumped up together and if 3rd and 4th CheckBoxes were checked in response to a question ... in the ResponseTable those will be shown as items C. and D.

In the following solution, I used the COUNTIF function to sum up the responded to CheckBoxes (out of 5), I am going to count how many of A., B., C., D., and E. are present in an answer. So here we go ...



The formula in cell H2 of sheet yogi_ECAW Raw Scores is:
=sum(countif(G2;{"*A.*","*B.*","*C.*","*D.*","*E.*"}))
this formula is then copied down to cells H3, H4, ...
---------------------------------------------------------------------------------

Kim (kasumner) commented ...
I finished formatting the cells. The new data points entered from the form are not entered onto a row with the formula; it creates a new row. How can I get the new data to enter onto a row with the formulas?


Kim, for that we will need an arrayformula ... so I have added the sheet yogi2_ECAW Raw Scores. I added a blank row 2 and I added the following formula in cell H2 ...

=ArrayFormula(if(len(G2:G),mmult(iferror(sign(find({"A.","B.","C.","D.","E.","F."},G2:G))),sign(transpose(column(A1:F1)))),iferror(1/0)))

I added the formula in cell H2 of a blank row, so the formula will apply even to the first Form submittal  logged in the Response Table.

yogi_MultipleSUMIFsProblem

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

timm.acdd said ...
I am having difficulty trying to get google docs to recognize my sumifs formula. I have read that it doesn't have a sumifs formula and I should just add them instead. This doesn't work for my formula. This is what I basically need:

I need cell to look in column 'Client Table'!$E:$E, check to make sure it is the same as the column that my cell is in (C$3), then check to see if there is a "1" in column 'Client Table'!$H:$H. Once it checks these 2 columns for those criteria, then it should sum column 'Client Table'!$D:$D.

In essence, check the 2 columns and add together everything that the 3rd column has in it.

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

Google spreadsheet does not have SUMIFS function ... in the following solution, I have used the SUMPRODUCT function to deal with multiple SUMIFs



I have also added sheets UsingQUERY, and UsingFilter.

yogi_FindWhichColumnHasMaxNumberOfEntries

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

rajvivek said ...
I have a spreadsheet in which the 1st row (A1:AL1) has different headers - say Header1, Header2, Header3 etc. The sheet is long till AL100 in which some cells are empty, and some are non-empty.

I want a formula that could tell me in A101:B101 - 1) the name of the column (for example, Header2) which has the highest no. (instances) of non-empty cells and, 2) Total no. of such instances (non-empty cells) of that particular column.
------------------------------------------------------

for developing this solution, I used a named range yRange for data starting from row2 down. And for this illustration, yRange has data in cells A2:L8

yogi_QueryWithOpenEndedColumnAndRowRanges

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

I am going to address here a case where not only the rows range is open-ended, but also the columns range is open-ended in that one can add more fields (columns) 

because the queried range can expand both horizontally and vertically, I thought it is best to take the SourceData in one Sheet and run the QUERY in tne ResultsSheet

What I am trying to compute here are Day totals (Day1, Day2,Day3, ....) for unique items in column A.

So, here we go ...

If more fields (columns), say Day4, Day5, etc. are added to the SourceDta, the Results Sheet will self adjust and expand to suit.

yogi_QueryFunctionWithColumnAndRowCriteria

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

parefr said ...
I am trying to use the filter function with a condition base on a colum and a condition base on a row. The data look like this:


Row
1 Item Color Day1 Day2 Day3
2 AAA   Red     5    0    3
3 AAA   Blue    1    2    4
4 AAA   Yellow  6    2    1
5 BBB   Black   0    2    3
6 BBB   Red     7    6    2


The results I would like is:


Row
9  Item Day1 Day2 Day3
10 AAA   12     4    8
11 BBB    7     8    5
------------------------------------------------


In the following solution I have used the QUERY function

parefr commented ...
I see what your query is doing, but I will have to change the query every time I add a new day. Is there another way to do it?


Yes we can change the range from A1:E6 to A:E ... I have added Sheet2 to illustrate this -- the formula in cell G1 is ...
=query(A:E,"select A,sum(C),sum(D),sum(E) where A<>'' group by A label sum(C) 'Day1', sum(D) 'Day2', sum(E) 'Day3' ")

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

yogi_TotalHoursByDayFromSortedListOfDateAndTime

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

Gen 3 Electric said:

I have a form that is giving me date, time and start and stop time. I want to see when the date changes then find the difference from the first time entered from the last time entered for each date. 


Date      Time as   Answer I'm looking for

                 a #

1-1-2011     7.5
1-1-2011     8.5

1-1-2011     11

1-1-2011     16            8.5

1-2-2011     7.5

1-2-2011     12

1-2-2011     16.5          9

1-3-2011     7.5

1-3-2011     11

1-3-2011     13

1-3-2011     14             6.5

--------------------------------------------------
In Sheet1 I used Gen 3 Eletric's source data using the range of values in cells A2:B12 with the following formula in cell C2 ...
=ArrayFormula(if(A2=A3,"",max(if(A$2:A$12=A2,B$2:B$12))-min(if(A$2:A$12=A2,B$2:B$12))))

--------------------------------------------------
Gen 3 Electric commented ...
I'm not sure where I'm where I'm going wrong. I can see how it can work. Thanks for the direction. I tried upping range from A12 to A51. That is because I have a few hundred entries. Could I use A:A?


I added Sheet2 to use open ended ranges A2:A, and B2 using the following formula in cell C2 ...
=ArrayFormula(if(A2=A3,"",max(if(A$2:A=A2,B$2:B))-min(if(A$2:A=A2,B$2:B))))