Quote:
Originally Posted by jdkoerner
What I am looking to compute is exclusively in the AB column, with the header "Final D score".
|
Ohhhhh...AB column not AB stat. :-)
Quote:
|
In this column I need the average of the values that are not hidden, and the standard deviation of these values. When I entered AVERAGEIF (AB3:AB97, TRUE, AB3:AB97), I got a division by zero error. When I entered AVERAGEIF(AB3:AB97, TRUE), I got a division by zero error. AVERAGE results in the averaging of both the unhidden and hidden cells in column AB.
|
Your formula is looking for the value TRUE in Final D score. In Excel, TRUE has a value of -1. Since it's not finding any i.e. N=0, it returns #DIV/0!. It looks like the NL players all have 0 for this, right? In that case, the formula should be:
The formula should be:
=AVERAGEIF(AB3:AB97,">0") which equals 10.86
AFAIK, there is no STDEVIF function.
See the attached mucking about. I applied the Format as Table functionality. I applied a filter to Final D score so no zeroes are displayed. Look at the totals row at the end. Click the dropdown and you can see several builtin functions for this cell: Sum, Average, Stdev.
It is currently set to Average. Note the value: 10.86. The Subtotal formula only applies to the data currently allowed by the filter. If you apply other filters (e.g. Team = CLE), the average will update to reflect those data only.
Now choose Stdev. There's your answer: 4.918. Nice huh?
Also, look at the formulas you wrote for Final d score. With Format as Table automation, the formulas don't use cell addresses. They use the name of the variable as provided in your column headers:
=[@[prorate%]]*SUM(Stats[@[r2]:[sb5]])+[@ba6]
Another neat thing, if you insert a new row to add data, the formatting scheme is maintained automatically. And you can change the formatting scheme to whatever you want. I'm currently in my "green period" so this is the style I prefer. But there are dozens to choose from and you can even design your own.
Anyhoo, let me know if you need more help.