Sumproduct function

[adsxvii]

In this file I have a team in column A, in B are the relative number of games they have played, and in C the filed goal % for them in that game. The value I am trying to return the average field goal % for that team in there last 10 games. I thought the sumproduct would work but is not returning a value, any help would be appreciated. Thanks in advance Andy

[NBVC]

Please save your file as Excel2003 and rezip and attach...

[adsxvii]

I changed it sorry first post with an attachment

[NBVC]

I get a value of 36.96%... Is that wrong?

[adsxvii]

I guess I must have some kind of format problem that is the right number but I am getting a #VALUE! error

[NBVC]

I think it is the header in column C causing your Value error... since you are using whole column references in your formula, you are including the column header in the multiplying of the separate arrrays...and you can't multiply text strings by numbers....

if you change all your ranges to definite ranges that start at row like C2:C65536, then you should get the right result.

[Dean England]

You can't use full column lookups on sumproduct or array formulas. Your formula needs to be written as:

Please Login or Register  to view this content.

or whatever the column depth is, but you can't use A:A, B:B, etc.

Dean

[NBVC]

You can't use full column lookups on sumproduct or array formulas. Your formula needs to be written as:

Please Login or Register  to view this content.

or whatever the column depth is, but you can't use A:A, B:B, etc.

Dean

Not if you have 2007...which I think the OP does have...but in this case, they are getting the error because of the specific element in the array being a text string and trying to get multiplied by numbers..that is causing the error here.

[adsxvii]

Ok I think I had some cells maybe formatted funny it works now. A new problem has risen. I need 2 things from this project, the first I have done getting the average of the last ten games. The next thing I need is to get the median of the last ten games. I have added a 2 sheet file to show where I am at any ideas would be grateful thanks in advance Andy

[daddylonglegs]

Normally averaging averages isn't a very good idea because it can skew the results. For instance, look at a situation where you have only 2 games. In one game there is only one FG, it's successful so % is 100%. Second game has 10 field goals, 5 of them kicked giving a % of 50.

If you average 50% and 100% you get 75%....but in fact across the 2 games there were 11 field goals, 6 successful, a true % of approx 54.55%.

I wouldn't be convinced about calculating a median across 10 games either but, if you want to, try

=MEDIAN(IF(Database!$A$2:$A$200=A2,IF(Database!$B$2:$B$200>D2,IF(Database!$B$2:$B$200<E2,Database!$C$2:$C$200))))

This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER

BTW, if you are using Excel 2007 you could still use whole columns for your first formula, just need to alter the syntax slightly, i.e.

=SUMPRODUCT((Database!A:A=A2)*(Database!B:B>D2)*(Database!B:B<E2),Database!C:C)/B2

[adsxvii]

Thanks Daddylonglegs that works perfectly. Wow I have learned so much from this site. Thanks again guys

[NBVC]

...

BTW, if you are using Excel 2007 you could still use whole columns for your first formula, just need to alter the syntax slightly, i.e.

=SUMPRODUCT((Database!A:A=A2)*(Database!B:B>D2)*(Database!B:B<E2),Database!C:C)/B2

Yes, sorry adsxvii. I don't know what I was thinking. The last array, which is just the array to be summed, should be preceded with a comma instead of an asterisk... then you could use the whole column references as mentioned by daddylonglegs. I guess in the translation from 2007 to 2003 I missed that.