11 Replies Latest reply: Oct 5, 2013 12:58 AM by kansaiben
Level 1 (0 points)

This is probably a very simple thing to do, but I haven't managed to find a solution yet. Someone please help!

I have a spreadsheet to calculate grades for my students.

I enter their raw scores, which is then calculated into a percentage (which I then adjust, depending on the raw average).

I then use an IF formula to award a grade between 1 and 10 based on that adjusted percentage.

 1AB ASSESSMENT TASK Speech 1 Speech 2 Speech 3 TOTAL FINAL GRADE Term 1 MAX GRADE 10 10 10 30 % ADJ. % Student A 5 7 7 19 63% 67% 7 Student B 9 7 7 23 77% 81% 8 Student C 9 7 9 25 83% 88% 9 Student D 4 5 5 14 47% 49% 5 Student E 6 9 6 21 70% 74% 7 Student F 7 5 5 17 57% 60% 6 CLASS AVERAGE 6.7 6.7 6.5 19.8 66.1% 69.8% I want an average here! 66.7% 66.7% 65.0%

I would like to know the average of the grades awarded, but the AVERAGE formula does not work (I'm guessing because the numbers are as a result of a formula, not actual data typed in by me).

Can anyone help please!?

Numbers , OS X Mountain Lion (10.8.5)
• Level 6 (8,535 points)

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.

• Level 7 (29,955 points)

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

• Level 1 (0 points)

Thanks 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?

• Level 1 (0 points)

Thanks, Jerrold. Appreciate the advice!

• Level 7 (29,610 points)

"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

• Level 1 (0 points)

Hi 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.

• Level 6 (8,535 points)

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

Regards,

Ian.

• Level 1 (0 points)

Thanks, Ian.

All good now. Cheers.

• Level 6 (8,535 points)

Glad it worked!

Ian.

• Level 7 (29,610 points)

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

• Level 1 (0 points)

Barry, thank you kindly. That is a much more elegant solution.

Appreciate all the help!