Calculations based on VLOOKUP?

[bp32]

I have about 30,000 rows of data, each row has performance data for a single day for an athlete. The days--and rows--vary by athlete. I would like to do some calculations on another sheet based on the VLOOKUP function for each player. For example, if I want to calculate the standard deviation of a particular column, but want the calculation to only include rows based on a specific player's name, what would the formula look like?

I tried this: =STDEVP(VLOOKUP(A3,DAY_TO_DAY2009!$C:$W,21,FALSE)), where A3 is the athlete's name, Day_to_Day is the reference sheet, 21 is the column where the data resides, but it doesn't seem to work. I need the formula to calculate the standard deviation of data in column 21, but only for the specific name in A3.

Any suggestions?

 

[bp32]

This is weird.

I am taking the STDEV of just the cells that correspond to the first player to see what it should be returning (i.e. =STDEV(X11:X152).

What's funny is I took a small portion of the data and dumped it into a google doc and used their array method and guess what, it worked. Take a look. (Scroll to columns X and Y for the formulas). This should also give you an idea about my data in the larger excel sheet.

This type of approach is valid for many functions, e.g. if you wanted the maximum value in column X for the name in column C then you could use exactly the same formula but with MAX instead of STDEV, or with AVERAGE instead of STDEV for the average.

How are you manually calculating the STDEV, are you sure that's accurate?

An alternative method (but it should give you the same results as the previous array formula) would be to use the database function DSTDEV.

That requires you to have unique headers in the columns (in row 1). Assuming you have the header Name in DAY_TO_DAY2009!C1 you can use this formula in the Summary worksheet

=DSTDEV(DAY_TO_DAY2009!C1:X30000,DAY_TO_DAY2009!X1,A3:A4)

where A4 contains the name as before and A3 matches the header in DAY_TO_DAY2009!C1, i.e. Name.

You can use other cell references for the criteria but it must be the column header of the name column in one cell and the name in the cell below

edit: Oh, and a question.....How are the names distributed, are the names in blocks so that all instances of each name are in a single range, or can they be sorted so that they are that way? Both formulas I suggested work whether they are like that or not but it might be more efficient to use a method that would find a specific range of data rather than looking at 30000 rows......

 

[barry houdini]

All I can see that might be different between Googledocs and excel is how blank cells are treated. Do you have some rows with a name in column C and a blank in column X?

If you do then the array formula approach in Excel will treat these as zeroes, which skews the Standard Deviation result (whereas all the other approaches - googledocs, DSTDEV and STDEV on a fixed range - will ignore blanks)

If that's the problem then you can try amending the array formula along these lines:

=STDEV(IF(DAY_TO_DAY2009!C2:C30000='Summary Calculations'!A4,IF(DAY_TO_DAY2009!X2:X30000<>"",DAY_TO_DAY2009!X2:X30000)))