
Yellowbox Oct 4, 2013 5:53 AM
Re: How do you use AVERAGE based on values from an IF formula? in response to kansaibenHi 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.

Jerrold Green1 Oct 4, 2013 11:31 AM
Re: How do you use AVERAGE based on values from an IF formula? in response to YellowboxBen,
I'll weighin 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

kansaiben Oct 4, 2013 3:16 PM
Re: How do you use AVERAGE based on values from an IF formula? in response to YellowboxThanks for your response!
I have simplified the sheet so it contains no split or merged cells, yet still I get the exclamation mark where I want the average. (The exclamation mark says 'Numbers can't be divided by zero'.)
Exam
Skit
Exam
Class Participation
1
2
TOTAL
MAX. MARK
55
30
100
15
200
%
ADJ. %
MARK
Student A
37
20
84
15
156
78%
72%
7
Student B
39
28
91
15
173
87%
80%
8
Student C
39
28
86
10
163
82%
76%
8
Student D
34
26
92
15
167
84%
78%
8
Student E
38
26
74
10
148
74%
69%
7
CLASS AVERAGE
37.4
25.6
85.4
13.0
161.4
80.7%
75.0%
!
I guess the basic question is: can you actually use AVERAGE on data that is output by an IF formula?

kansaiben Oct 4, 2013 3:16 PM
Re: How do you use AVERAGE based on values from an IF formula? in response to Jerrold Green1Thanks, Jerrold. Appreciate the advice!

Barry Oct 4, 2013 4:18 PM
Re: How do you use AVERAGE based on values from an IF formula? in response to kansaiben"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

kansaiben Oct 4, 2013 8:38 PM
Re: How do you use AVERAGE based on values from an IF formula? in response to BarryHi Barry,
I am the formula =AVERAGE(K5:K31) to work out the average.
The formula in the cells above that display the mark is:
=IF($J13<=14%,"1",IF($J13<=24%,"2",IF($J13<=34%,"3",IF($J13<=44%,"4",IF($J13<=54 %,"5",IF($J13<=64%,"6",IF($J13<=74%,"7",IF($J13<=84%,"8",IF($J13<=94%,"9",IF($J1 3>=95%,"10",))))))))))
Thanks for your help.

Yellowbox Oct 4, 2013 10:23 PM
Re: How do you use AVERAGE based on values from an IF formula? in response to kansaiben SolvedYour IF formula is inserting numbers as text e.g. "1" Try 1 instead.
Regards,
Ian.

kansaiben Oct 4, 2013 11:01 PM
Re: How do you use AVERAGE based on values from an IF formula? in response to YellowboxThanks, Ian.
All good now. Cheers.

Yellowbox Oct 4, 2013 11:05 PM
Re: How do you use AVERAGE based on values from an IF formula? in response to kansaibenGlad it worked!
Ian.

Barry Oct 5, 2013 12:02 AM
Re: How do you use AVERAGE based on values from an IF formula? in response to kansaiben HelpfulIan'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 iffalse 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

kansaiben Oct 5, 2013 12:58 AM
Re: How do you use AVERAGE based on values from an IF formula? in response to BarryBarry, thank you kindly. That is a much more elegant solution.
Appreciate all the help!