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?

 

[TMShucks]

VLOOKUP will only return one value.

Maybe (untested)

=STDEVP(SUMPRODUCT(--(DAY_TO_DAY2009!$C2:$C1000=A3),--(DAY_TO_DAY2009!$W2:$W1000)))

Regards

 

[barry houdini]

I think you'd need an array formula like this

=STDEVP(IF(DAY_TO_DAY2009!C$1:C$30000=A3,DAY_TO_DAY2009!W$1:W$30000))

confirmed with CTRL+SHIFT+ENTER

 

[Marcelo Branco]

Hi,

Assuming your data in C3:W30002, i think you need an array-formula like this
=STDEVP(IF(C3:C30002=A3,W3:W30002)
Confirmed with Ctrl+Shift+Enter simultaneously (not just Enter)

I'm not sure about the perfomance of this formula with 30.000 rows...

HTH

M.

 

[bp32]

Thanks, everyone.

I tried the array formula =STDEV(IF(DAY_TO_DAY2009!C2:C29206='Summary Calculations'!A4,DAY_TO_DAY2009!X2:X29206)) and it seems to be calculating, but the product is off. The STDEV comes back as .10585 for the first player, but when I manually compute the STDEV for that player the product is .068016.

Anything else I might be missing? The data is on one sheet and the name I want to make conditional (A4 in the formula) is on another one.

Thanks!

 

[barry houdini]

You need to "array enter" the formula.

Select cell with formula. Press F2 key and then hold down CTRL and SHIFT and press ENTER. If done correctly you'll see curly braces like { and } around the formula in the formula bar....and you should get the correct result

 

[bp32]

Hi Barry,

That's actually what I did (sorry, the curly brackets disappeared when I went to select the formula and I didn't retype them). Here's exactly what I did:

{=STDEV(IF(DAY_TO_DAY2009!C2:C30000='Summary Calculations'!A4,DAY_TO_DAY2009!X2:X30000))}

It looks like regardless of how many rows I limit the array to it always comes back .1055 based on the data I am working with. It isn't simply calculating every row as the STDEV for all the data in the X column is .0906.

 

[barry houdini]

OK, well the formula looks correct - I assume column X is right, you started with column W....?

Perhaps some of the names don't match due to misspellings or extra space characters. try checking that you get the number you expect with a COUNTIF formula.

=COUNTIF(DAY_TO_DAY2009!C2:C30000,'Summary Calculations'!A4)

Another possibility is that some of the numbers in column X are text formatted (and won't be recognised by the formula). Does it make any difference if you use this version?

=STDEV(IF(DAY_TO_DAY2009!C2:C30000='Summary Calculations'!A4,DAY_TO_DAY2009!X2:X30000+0))

 

[bp32]

Yes, sorry, Column X is correct. The names, or at leas the first one I tried, look fine. And the values in X are formatted as numbers.

I can tell the formula isn't forcing excel to focus in on only those values in column X with the corresponding player name in column C and then doing the calculation. Originally, I thought VLOOKUP would do it since it looks at the value in one column and zeros in on the corresponding values in another column, but it sounds like that won't work for a bundle of values.

 

[barry houdini]

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......