yogi_Add Practice Sessions Attendance

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

a user asked:
Help with conditional values.
So I have an attendance sheet for team practice and I'm trying to calculate the total number of practices, and each individual's percentage of attendance. I am trying to do that with out having to manually change the total practices field after each practice. What I'm trying to do is check to see if a range has any value >0, and then add it. So that when I put a 1 in the new practices column marking attendance, the total practices field updates automatically.
I've tried using if statements, but that doesn't seem to be working at all. I've tried using count, but I can't get it to work for me. I haven't used a spreadsheet in a while so I'm pretty rusty. Any help would be great.
C D E F G H
1 1 0 0 0 0
1 1 0 0 0 0
1 1 0 0 0 0
1 1 0 0 0 0
1 1 0 0 0 0
1 1 0 0 0 0
1 0 0 0 0 0
1 1 0 0 0 0
1 1 0 0 0 0
1 0 0 0 0 0
1 1 0 0 0 0 Total Practices : 2
----------------------------------------------------------
In the following I have proposed formulas for

• formula for row by row computation
• array formula for fixed number of Practice sessions
• array formula for dynamic range of Practice sessions
• array formula for open ended range of Practice sessions

yogi_Compute Average Of A Specified Attribute Of A Stock Over The Past Specified Number of Days

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

A user asked:
Calculate average of high - low for a stock over 50 days
---------------------------------------------------------------
In the following proposed solution I have generalized it in terms of attribute to be averaged and also the number of days past

Look Ma ... There Are No Columns In The Basement!

From my Energy Efficient Building Network blog ...

for viewing pleasure of my Cloud Computing -- Google Docs Way readers I present here a short video clip that I accidentally took when I was at the energy efficient healthy house we are building in southeastern Michigan. By the way I use primarily spreadsheet for design, documentation, reports, and  invoicing work for all of my projects

construction noise ... so please turn down the speaker volume

for more details on this energy efficient building project click on the following link:
http://energyefficientbuild.blogspot.com/2011/09/look-ma-there-are-no-columns-in.html

Enjoy!

Yogi

yogi_Compute Hours Worked By Date From Form Submittals And Display Those That Are Greater Than A Specified Number

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

question:
QUERY( QUERY( A:C ; "Select toDate(A), B, sum(C) Where B<>'' Group by B, toDate( A) ") ; "Select Col1, Col2, Col3 Where Col3>8 "; 1 )
I just found a problem with this formula,
Column A is timestamp date, Column B is a work code (Which doesnt matter), Column C is the hours worked.
I wanted a formula that showed all dates that had over 8 hours in a day, because sometimes there are multiple submissions in a day. This formula works, but I dont want it to factor in column B.
-----------------------------------------------------------------------
In my proposed solution in Sheet1 I have assumed that the work codes are TEXT strings

yogi_Compute Difference In Various Day Columns From BaseValues In Column A

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

Column A has BaseValues, Columns B, C, and D have values for Monday, Tuesday, and Wednesday. In column E I compute the difference for Monday Column values from BaseValues in Column A. Then the formula can be applied for computing for Tuesday, and Wednesday Columns
-----------------------------------------------------------

yogi_Check How Do Values Of Entries in Column A Compare With Those In Column B

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

Compare values of entries in column A with those in column B and post whether all of entries in column A are greater than those in column B
---------------------------------------------------------------

yogi_Create PivotTable From Form Data Get Counts By Specified Attributes Publish PivotTable

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

Data is submitted via a Form for Car Class, Drivers Name and Racing Yes or No.
Need to create a single PivotTable by Car Class and Drivers Name showing who is racing, who is not racing and their respective counts
----------------------------------------------------------
In the proposed solution here, I created two computed fields (shown in light brown background) to x mark Race or NoRace ... this works out nicely showing the needed information.

family and friends: Googling With Googlers At Google's Global Top Contributors Summit 13-14 Sep 2011

family and friends: Googling With Googlers At Google's Global Top Contributors Summit 13-14 Sep 2011: Googling With Googlers At Google's Global Top Contributors Summit 13-14 Sep 2011

yogi_Sum A Column From Row 2 Down And Use This Sum To Divide The Value In Specified Row Of Another Column

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

lunixer said:
How do I sum an entire column up to the row where I am and keep this formula consistent in later rows?
Okay, here's what I want to do:
In cell L3 I want to put the formula: =K3/SUM(C2:C3)
In cell L4 I want to put the formula: =K4/SUM(C2:C4)
Then I want to be able to paste this same formula throughout the entire L column, so that the summation always goes from C2:C# where # is equal to L#. However, when I paste this formula, eg from L3 to L5, it changes it to be L5=K5/SUM(C4:C5). And when I highlight the first two cells with this formula and drag, it maintains a three cell difference. So L6=K6/SUM(C4:C6). Do you have any idea how I would make this automatic?
---------------------------------------------------------

yogi_Lookup Address Changes For Same Names In Sheets 1 And 2 And Paste Original And Changed Addresses In Sheet3

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

veritasins said:
I have a contact list in Sheet 1 with the columns A-D and the titles Name, Address, Phone, Email. I have a similar contact list in Sheet 2. I need to know if there are any differences in the two sheets (need to find duplicates from Sheet 1 Column B and Sheet 2 Column B)....Specifically the Address Column. The perfect situation would be to be able to to show the entire contact that is NOT duplicate in Sheet 3 with Name, Address, Phone, and Email.
The way this works is I am downloading a contact list each month and I need to find out if there are any differences between the two lists each month to see if anyone has changed their address.
If I needed to, I could just have Sheet 2 Column E show the word Duplicate if that is an easier formula.
I was hoping that this formula posted by A.P.L. would work - =ArrayFormula( IF( LEN( A:A ) * ( COUNTIF( A:A ; A:A ) > 1 ) ; "Duplicate" ; IFERROR( 1/0 ) ) ) and I could just add Sheet2! to one of the formulas, but I can not get it to work.
I also tried a formula by yogia but I could not get it across two sheets either.
Browser & Operating System (Chrome):
Using Free Google Apps
-------------------------------------------------------------

yogi_Select Names Of Top Scorers

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

saxxoo said:
selecting names with highest score
I got this formula I'm using for selecting a name (B row) with the highest number (H row)
I looked through the formulas without finding anything that seemed to fit my need, so is there any way to get it to give out like the best
5 names instead of just the one with the highest number.?
=ARRAYFORMULA(SUBSTITUTE(CONCATENATE( FILTER(B2:B91; H2:H91=MAX (H2:H91))&", ")&",";", ,";""))
-----------------------------------------------------------------

yogi_Extract and Collate Other Data From A Form Based Spreadsheet

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

Cammo said:
How can I extract and collate "other" data from a form based spreadsheet?
I've tried searching for an answer to this, but have thus far failed to find anything relevant. I'm quite surprised, because I can't be the only person who's wanted to do something like this...
I have a form based spreadsheet that has radio button questions with answers like: Yes, No, Indifferent, Other. It also has check box type questions with answers like: Apple, Banana, Mango, Other.
In the above scenarios, somebody could click the "Other" radio button and enter "Depends on my mood". Equally, with the check boxes they might tick Apple, Mango and Other, and then enter "Tomato" for the "Other" entry.
What I want to do is extract these "Other" responses and collate them under a corresponding question heading.
An example of results might be something like:
--begin--
Other responses:
Do you like to go out?
Depends on my mood.
Only on Fridays.
What's your favourite fruit?
Tomato
Grapefruit
Orange
--end--
With the radio button results, I could look for something that's not /Yes|No|Indifferent/ and take action based on that, such as copy to a list on another sheet.
It seems trickier with the check boxes, as I'd only want to copy out content that isn't /Apple|Banana|Mango/ for example. I'm not sure how I can go about this.
Also, the methods above seem very labour intensive in as far as I'd have to copy all the non "Other" text into formulas by hand to get it to work. Is there any way to reference the form text directly from the spreadsheet?
Thanks in advance for any help.
------------------------------------------------------------

yogi_Sum Up Entries In A Range To Exclude Items As Specified

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

Nomad65 said:
I want to add up the contents of a column conditional on the value in another column
I'm creating a spreadsheet of monthly expenses, and I want to be able to selectively include/exclude certain entries based on the contents of another cell in the same row. For example, let's say column E contains the expense amounts, and column F contains a code such as "house" or "car".
I have a total for "all monthly expenses", but now I want to create totals for things like "all monthly expenses if I no longer had a car" -- and for that one, I'd exclude all the column E entries with the note "car" in column F.
I could do it brute force with a nasty set of "IF" statements. And of course I could copy the entire table and add a formula that changes the values to zero depending on the contents of column "F". But I'd like to do it more elegantly, and in a way that easily expands if I add new expenses to the spreadsheet. I have the sense that some combination of VLOOKUP and IF could do it, but I can't figure it out.
Thanks for any help!
--------------------------------------------------------------

yogi_Make Validation List Of A Cell Dependent On The Validation In Another Cell

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

WvdR said:
How do I make the validation list of one cell variable dependent on the validation in another cell
I'm using Google Docs Spreadsheet and have the following problem.
I'm trying to put together a logfile for my team in which I have 5 main categories and several subcategories of issues.
So for example:
A1: Categorie
A2: either job unfinished, job done, job delayed, job cancelled.
I used the 'validate data' function with a list to create a drop down menu.
B1: Cause (sub-categorie)
B2: depends on what I choose in A2. E.g. when I choose job unfinished, B2 should have a value of 'technical error, human error, deprioritised, other', whereas when A2 would have been 'job cancelled', B2 should have a value of 'customer's request, company's decision, other).
Right now my B2 cell has a drop down list consisting of all the different subcategories, but I would like to make the validation list dependent on my choice in A2.
If anyone has any suggestions, that would be great! Thank you very much in advance, appreciate it a lot!
---------------------------------------------
In my proposed solution, I have used Data Validation as well as Named Ranges as delineated in the spreadsheet

yogi_Flip Contents Of Two Columns With A Single Formula And Post Results In Another Sheet

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

rajvivek said:

How to Flip two columns contents with a SINGLE formula?

How is it possible to have a SINGLE formula to pull the content from column A to column D and from B to column C?

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

There is no direct way to do it ... however
A.  1) I can do it in another sheet, say Sheet2
      2) if column1 row1 of data is blank (or inconsequential)
      3) if there is a blank column available to the left of where data would be posted in Sheet2
or
B.  It can be done using the QUERY function, as shown in Sheet3

so here we go ...

yogi_Sum Up Specified Attribute Of Like Items In a Separate Sheet

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

DaveCable said:
Sorting and Adding
Hello I am trying to like items and their assiciated data on one sheet and sum the results on another sheet.
I would like a formula that searches for color, grabs the total weight for that color and adds those weights together.
I am trying to use vlookup, and it's great at returning the first value but I can't quite figure out how to get all results.
The formula I am working on is in B2.
Right now I have: =vlookup(A2,Sheet1!A2:I100,9,false)
https://docs.google.com/spreadsheet/ccc?key=0AjvC40DK4abZdHVENE93THF4UDZ0cGtCVENoYk5oZWc&hl=en_US
Any help would be appreciated!
---------------------------------------------------
I have added alternate solutions in several different sheets ...

yogi_Compute Stats Such As Wins Losses Etc For Games Played By Specified Teams

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

Shawn242 said:
Making a score sheet, referencing data help
I am making a scoresheet, with 3 different sheets. Schedule, Standings, and Player Statistics. What I'm trying to do, is have the Standings "call" from the Schedule, and get each win for a team, total it, then place that total in the Standings sheet. Is this possible?
------------------------------------------------------------------------

yogi_Combine Text From Specified Columns And Extract AlphaNumeric Characters Only

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

ExtraMediumInc said:
how do i take the combine the text values in columns A and C, remove spaces, and put into column J for all 3000+ rows?
I have a spreadsheet filled with information of over 3000 people. I want to make an extra column (J) that combines the text in columns A and C in each row, removing any spaces, periods, commas, dashes, etc.
--------------------------------------------------------------

In this proposed solution I used RegexReplace function to negate replacement of all AlphaNumeric Characters which of course results in extracting only AlphaNumeric characters