11 Replies Latest reply: Oct 5, 2013 12:58 AM by kansaiben
kansaiben Level 1 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)
  • Yellowbox Level 5 Level 5 (7,590 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:

     

    Screen Shot 2013-10-04 at 10.46.32 PM.png

    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 Level 7 Level 7 (29,945 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

  • kansaiben Level 1 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?

  • kansaiben Level 1 Level 1 (0 points)

    Thanks, Jerrold. Appreciate the advice!

  • Barry Level 7 Level 7 (29,350 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

  • kansaiben Level 1 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.

  • Yellowbox Level 5 Level 5 (7,590 points)

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

     

    Regards,

    Ian.

  • kansaiben Level 1 Level 1 (0 points)

    Thanks, Ian.

     

    All good now. Cheers.

  • Yellowbox Level 5 Level 5 (7,590 points)

    Glad it worked!

     

    Ian.

  • Barry Level 7 Level 7 (29,350 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.

    Screen Shot 2013-10-04 at 11.57.03 PM.png

    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 Level 1 Level 1 (0 points)

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

     

    Appreciate all the help!