When I use =average on a column of numbers I'm getting the wrong result. By using sum and then dividing the number of cells i get an entirely different answer. Excel says 5.49. I say 10.64. I know mine to be right because when I do a standard deviation from the mean I get results that make sense. The other number simply doesn't work. So where is excel getting it?

Post the sheet please.

I figured out the problem. I have hidden rows that are being used in the calculation. I understand that the averageif function will use only the visible rows. I've looked at several sites trying to understand how to set up the three arguments to use averageif, but I'm not quite getting it.

If you post your sheet as Gebobs suggested, we might be able to help

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

What could be easier than AVERAGEIF?

Here's the first 100 rows of the sheet. The rows that display are AL outfielders; the hidden row are NL outfielders.

So my understanding is that averageif takes three arguments: the first is the range of values [in this case, all the cells in column AB]; TRUE, which I understand references those cells that are seen, and then one last argument, which is another range of cells. Here's where I get lost: on this sheet, where does the final argument come from?

Column AC is supposed to be Z values, so I also need STDEVIF for column AB as well, so I can then calculate the standard deviation from the mean for each AL player.

Thanks!

see attached

Attached Files

OF stats example.xlsx (41.5 KB, 12 views)

How did you hide the NL rows? Manually?

Are you just trying to get the average and stdev for AB by league? To use averageif for this, you would need a to distinguish between AL and NL. As the table is, you don't have that.

=AVERAGEIF(range,criteria,averagerange)

range is the range that you are testing, in this case which league each player is in.
criteria is what you are testing for, in this case the leagues, AL and NL.
averagerange would be what you want to average, in this case AB.

In some cases, the first and last argument are the same, say for instance if you only wanted to average those player's AB who had 50 or more. In that case, the last argument is not necessary.

So what you need to do is specify the league affiliation for each player.

I would use the Format as Table function right away. It would make all this so much easier. I would do it for you but this is only a sample table and the work would be wasted since you would need to add the other players anyway.

Unfortunately, formatting the data as a table is still giving me the wrong result. And I haven't been clear on what the data is I'm interested in.

Yes, I did manually hide each row that was for an NL player. So league affiliation is already accounted for. What I am looking to compute is exclusively in the AB column, with the header "Final D score". 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.

Do feel free to muck about with the sheet. Anything you do I can cut and paste into a larger sheet with more values, or just to see it would tell me what I needed to do to apply it to the full sheet

Ohhhhh...AB column not AB stat. :-)

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.

Attached Files

OF stats example.xlsx (32.5 KB, 12 views)

When I converted the data to a table in the same sheet as you did, the filter on top of column AB didn't have a zero option to check or uncheck; what it had was "blank". so is there a particular type of table I should be using?

And, how did you create that nifty total row with the pull down functions? Is that also a function of the table itself?

That may be because you manually hid the data. If not, then you need to look at what is in that column for the NL players. If it's no longer zero as in the data you posted earlier, then you'll have to find another way to differentiate between the leagues if that's still what you need.

The total row can be added after you Format as Table. Go to Table Design on the ribbon, Table Style Options, check Totals row.