How do you use AVERAGE based on values from an IF formula?

Hi kansaiben,

I'm guessing because the numbers are as a result of a formula, not actual data typed in by me

I'm guessing that your formula does not work because you have merged cells. Keep your table simple:

No merged cells. D1 is a cell in a Header Row. D8 is a cell in a Footer Row. That keeps the formula confined to just the Body Cells D2 to D7.

Or if you don't want Header or Footer Rows, use the formula

=AVERAGE(D2:D7)

Regards,

Ian.

Ben,

I'll weigh-in with Ian on the layout of your table. It's clear that you have spent much time formatting it carefully to enhance your message. You will be better off in the long run if you never, ever, split or merge cells. Pages has some nice features, and even some rather pedestrian ones, that simply won't work in the presence of merged and/or split cells.

Jerry

"I guess the basic question is: can you actually use AVERAGE on data that is output by an IF formula?"

The basic answer is Yes. Can we see the formula you are using in the cell with the exclamation mark?

Regards,

Barry

Your IF formula is inserting numbers as text e.g. "1" Try 1 instead.

Regards,

Ian.

Glad it worked!

Ian.

Ian's observation could also explain the source of the Division by Zero error reported in the error message. COUNT, which is one of the functions comprising AVERAGE, counts only number values (or duration values, where the average is of a set of durations). Text values are not counted, so if all the inserted values are Text, their count, which is used as the divisor in calculating an average, will be zero, and the function will return an error.

Ian suggests removing all the quotation marks around the number values in your formula. If you choose to keep the nested IFs formula, another thing you should look at is the last two IFS:

IF(J13<=94%,9,IF(J13>=95%,10,))

What happens if J13 is 94.3%?

The formula can be shortened by eliminating this last IF statement entirely. If the formula gets to that section, it has already tested for every possible value up to and including 94% and found all of those tests returned FALSE. The only possibility left is that the tested value is greater that 94%, and the grade for that level (10) can be used as the if-false argument in IF(J13<=94%,9,10)

But I'd go further, and suggest throwing out the nested IFs formula as difficult to write and difficult to edit. and replace it with a much simpler LOOKUP formula and accompanying Lookup Table. Here's what it would look like. I've assumed that you want the mark to change at 15, 25...85, 95, and not at 14.0000001, etc.

Entries in column J (except for the cell in the footer row) are entered values.

Column K values are all calculated using this formula:

K2: =LOOKUP(J,Lookup :: $A,Lookup :: $B)

Fill the formula down to the last data row.

Row 11 is a footer row. It contains the same formula in both cells showing values.

J11: =AVERAGE(J)

K11: =AVERAGE(K)

Regards,

Barry