Formula for Grading - Driving me crazy! What's wrong here?

[dougg]

Why does this formula work for translating student course averages into
letter grades:

=IF(D5>92.5,"A",IF(D5>=89.5,"A-",IF(D5>=86.5,"B+",IF(D5>=82.5,"B",IF(D5>=79.5,"B-",IF(D5>=72.5,"C",IF(D5>=69.5,"C-",IF(D5>=62.5,"D","F"))))))))

but this one says there is an error (when all I'm trying to do is add
the C+ score to the same formula). It says there is an error and
highlights the last 'IF':

=IF(D5>92.5,"A",IF(D5>=89.5,"A-",IF(D5>=86.5,"B+",IF(D5>=82.5,"B",IF(D5>=79.5,"B-",IF(C5>=76.5,"C+",IF(D5>=72.5,"C",IF(D5>=69.5,"C-",IF(D5>=62.5,"D","F")))))))))

Any help would be much appreciated. This really messed up the grades I
just handed in!

Thanks,
Doug

 

[Paul Berkowitz]

Why does this formula work for translating student course averages into
letter grades:

=IF(D5>92.5,"A",IF(D5>=89.5,"A-",IF(D5>=86.5,"B+",IF(D5>=82.5,"B",IF(D5>=79.5,
"B-",IF(D5>=72.5,"C",IF(D5>=69.5,"C-",IF(D5>=62.5,"D","F"))))))))

but this one says there is an error (when all I'm trying to do is add
the C+ score to the same formula). It says there is an error and
highlights the last 'IF':

=IF(D5>92.5,"A",IF(D5>=89.5,"A-",IF(D5>=86.5,"B+",IF(D5>=82.5,"B",IF(D5>=79.5,
"B-",IF(C5>=76.5,"C+",IF(D5>=72.5,"C",IF(D5>=69.5,"C-",IF(D5>=62.5,"D","F"))))
)))))

Any help would be much appreciated. This really messed up the grades I
just handed in!

When you added in the C+ IF, you made an error with the column:

IF(C5>=76.5,"C+"

instead of

IF(D5>=76.5,"C+"

C5 instead of D5.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Michael R Middleton]

Doug -

Maximum of seven nested IFs in Excel.

Instead, use a lookup table.

- Mike

www.mikemiddleton.com

++++++++++++++++++++++++++

 

[dougg]

Right - The 'C' vs. 'D' error was only here (i didn't do it that way in
the actual table). So the mistake is the second one - a limited number
of nested IFs. Off I go to learn about 'lookup tables'.
Thanks for finding what was wrong (even though i was just hoping it was
a typo kind of error ).
-Doug

 

[dougg]

Right - The 'C' vs. 'D' error was only here (i didn't do it that way in
the actual table). So the mistake is the second one - a limited number
of nested IFs. Off I go to learn about 'lookup tables'.
Thanks for finding what was wrong (even though i was just hoping it was
a typo kind of error ).
-Doug

 

[dougg]

Well i've given the LOOKUP function a test with no luck.
Seems like it should be easy (but I can't get beyond #N/A).
Operating from just two students how would I do this ...

Assume many columns with various assignments ...
We arrive at Column F with a formula giving us the students' averages.
So for example ...
F G
1. STUDENT A .... 95 A
2. STUDENT B ... 85 B
....

Now what I'd like to appear in colunm G is the standard translation of
the numeric average in F into a letter grade.
I tried to build a LOOKUP formula in F6. Something like
=LOOKUP(F1,$J$1:$J$10,$K$1:$K$10)

I had put on the worksheet at the fixed location two columns. The first
column J listed the ranges against which the scores in column F would
be compared. Column K had the letter grades to appear.
It looked something like this:

J K
1. >=92.5 A
2. >=89.5 A-
3. >=86.5 B+
4. >=82.5 B
5. >=79.5 B-
6. >=76.5 C+
7. >=72.5 C
8. >=69.5 C-
9. >=62.5 D
10. <=62.49 F

Any ideas why this isn't working? (And take it easy on me - I'm an
anthropologist and only try my hand at these things on a 'need to know
basis'.)

Thanks, Doug

 

[Bob Greenblatt]

J K
1. >=92.5 A
2. >=89.5 A-
3. >=86.5 B+
4. >=82.5 B
5. >=79.5 B-
6. >=76.5 C+
7. >=72.5 C
8. >=69.5 C-
9. >=62.5 D
10. <=62.49 F

Check out Vlookup in Help. First, remove all the >= from the numeric grade,
so the column (K) contains only values. Then, make sure the letter grades
are in the column to the right (L) of the numeric grade. Then sort the 2
column table into ascending order. Change the first value to zero instead of
62.49. And, I assume that the student can get a grade other than an exact
value that's in the table, like 73 or 90, etc. In the column where you want
the letter grade to appear, place the following formula:

=vlookup(reference to the student's numeric grade, $k$1:$l$10,2)

 

[JE McGimpsey]

Why does this formula work for translating student course averages into
letter grades:

=IF(D5>92.5,"A",IF(D5>=89.5,"A-",IF(D5>=86.5,"B+",IF(D5>=82.5,"B",IF(D5>=79.5,
"B-",IF(D5>=72.5,"C",IF(D5>=69.5,"C-",IF(D5>=62.5,"D","F"))))))))

but this one says there is an error (when all I'm trying to do is add
the C+ score to the same formula). It says there is an error and
highlights the last 'IF':

=IF(D5>92.5,"A",IF(D5>=89.5,"A-",IF(D5>=86.5,"B+",IF(D5>=82.5,"B",IF(D5>=79.5,
"B-",IF(C5>=76.5,"C+",IF(D5>=72.5,"C",IF(D5>=69.5,"C-",IF(D5>=62.5,"D","F"))))
)))))

Any help would be much appreciated. This really messed up the grades I
just handed in!

One way:

Put a table, perhaps on another sheet (say, Sheet2):

A B
1 0 F
2 62.5 D
3 69.5 C-
4 72.5 C
5 76.5 C+
6 79.5 B-
7 82.5 B
8 86.5 B+
9 89.5 A-
10 92.5 A

Then if your final grade is in Sheet1, cell H2, for example, use

=VLOOKUP(H2,Sheet2!A:B,2,TRUE)

 

[JE McGimpsey]

Any ideas why this isn't working? (And take it easy on me - I'm an
anthropologist and only try my hand at these things on a 'need to know
basis'.)

See my reply to your earlier post.