yogi_Sum Up Games Played Against A Player By Win Lose Draw ...

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

yogi_How To Color Odd And Even Rows Of A Sheet

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

Let us say we want to color all even rows as gray, and all odd rows light brown then 

1.1) use the following formula in cell A1:

       =ArrayFormula(mod(row(YY:YY),2))

1.2) use the FILTER command to select all rows in column A for 0
     or optionally just select all the rows            

1.3) with all rows selected, change the background color to gray

and now to color all odd rows as light brown

2.1) remove the FILTER if you used one earlier

2.2) use the FILTER command to select all rows in column A for 1

3.1) with all rows selected, change the background color to light brown

on removing the filter, all even rows are colored gray and all even rows are colored light brown

now select column A and clear the contents of column A

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

yogi_Query Data For Specified Condition And Compute Count Sum And Average

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

user jimbo66 said:

How to calculate count, sum and average for an array

in this sample spreadsheet
col B_______Col E
Partname ___No parts made
A__________50
B__________100
A__________75
A__________80
B__________125
I am using the following function to count the number of times each part name occurs in column B
=QUERY(DATA!B2:F ; "Select B, count(E) Group by B Order by B ") to get
A__3
B__2
And =QUERY(DATA!B2:F ; "Select B, Sum(E) Group by B Order by B ") to get
A__205
B__225
then using the above data to calculate average number of parts made.
A__68.33
B__112.5
I want to get all three answers( count, sum, average) for each part name sorted by part name.
 Can I do this all in one function?
If I want to do this and only select where part color is red. Colors are listed in column C.
How do I do this?
https://docs.google.com/spreadsheet/ccc?key=0An1YqBusso3ZdGE3V3o0NDRicVlqX25TX0lOYnlKWlE
The data sheet is generated from a form.  I have stripped the data to make a sample data sheet.
On the report page the three items in the total column that are red are incorrect because I only want to total, count and average the rows in the data sheet where the operation is "in" and skip the rows where the operation is "out".
The columns generated by the query commands A-B and F-G will keep growing as different parts are made but I have to keep adding the compute functions for columns C and D.
If there is a way to compute sum, count and average in one command, the report will continuously be generated
with the correct number of rows

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

following is a solution to the problem

yogi_Create A Numbered List In A Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user johnnyTremor said:
How to create a numbered list 
I want to create a numbered list but do not see the icon on my toolbar.

I am using Google Chrome on a Windows 7 Home Edition desktop.

Here is what I am looking to do within a cell.

1. Plays well with others

2. Uses words to request help

3. Stays within the lines

So basically a numbered list of things

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

as of this time Google spreadsheet does not have a feature for creating a numbered list  ... so following is a workaround solution to the problem ... cells A2:A5 house the list of items that will make up a numbered list to be housed in cell B2:B5

yogi_Extract List Of Unique Advertisers That Are Attending By Sales Exec

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
use Donna said:
I am trying to compute the number of unique Advertisers (Column B, Sheet "Client List") booked to attend an event (= "Y" in Column P, Sheet "Client List") for each sales rep (Column A, Sheet "Client List"). 
My expected result for sales rep "Anthony Fonseka" would be 3 (02 PLUS SDN BHD, ACE SYNERGY INSURANCE BHD,TERRADERM SDN BHD) 
and result of 1 for sales rep "Kevin Choong" (ONLYONE ZUZA FOODS INDUSTRIES SDN BHD). 
Note that Advertisers TERRADERM SDN BHD & ONLYONE ZUZA FOODS INDUSTRIES SDN BHD occur twice but should only be counted once.
I hope that makes sense
Thanks a lot

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

Have a look at the following solution to the problem ... to see if I have understood you correctly

yogi_Formulas For Specified Functions To Remain Valid Even If Rows Are Inserted And Or Deleted

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user dpmacd said:
Automatically adjust for sum and average
Hi,
I have a spreadsheet. Column A is employee name. Column B is total number of training hours. Rows 2-10 are current employees. Row 11 is "Total departmental training hrs" =SUM(B2:B10). Row 12 is "Average training hrs per employee" =AVERAGE(B2:B10).
As we add new employees (insert new row) in alphabetical order to Column A, is there a way to set the formula (or maybe a different way of inputting a new employee) to automatically adjust itself accordingly so that the SUM and AVERAGE reflect all current employees plus the new employee? So that every time a new row is added, B10 in the formulas goes up by 1..
As we will have multiple people managing this spreadsheet, it would be great if we didn't need to remember to adjust the formulas every time a new employee is added.
Thanks much,
dpmacd
---------------------------------
following is a solution to the problem

yogi_Match Data In Columns Of Different Sheets And Combine Data Into Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user gmckinlay said:
Spreadsheet formula to combine sheet data needed
Hi,

I need a formula that combines data from 2 sheets in a 3rd sheet based on data in a common column in the 1st two sheets. This combined data is only certain columns from each of the sheets.

Sheet 1

Column A | Column B | Column C

Sheet 2 

Column 1 | Column 2 | Column 3 | Column 4

Sheet 3

Column A | Column 2 | Column 3 

Where Column B = Column 2

Thanks in advance for the help.

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

following is solution to the problem