If nested function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wouldlike to know if there is a better way to setup this equation to evaluate marks entered and convert to an alpha grade. Also on a fail mark they have the oportunity to rewrite hence the last.

IF(C4>96.9,"A+",IF(C4>93.9,"A",IF(C4>89.9,"A-",IF(C4>84.9,"B+",IF(C4>79.9,"B",IF(C4>74.9,"B-",IF(C4>69.9,"C","F",IF(OR(C4<69.9,C6>69.9,"B
+","F")))))))))

Thanking you all in advance for any advice you might be able to give.

Regards

Bill
 
I wouldlike to know if there is a better way to setup this equation to evaluate marks entered and convert to an alpha grade. Also on a fail mark they have the oportunity to rewrite hence the last.

IF(C4>96.9,"A+",IF(C4>93.9,"A",IF(C4>89.9,"A-",IF(C4>84.9,"B+",IF(C4>79.9,"B",IF(C4>74.9,"B-",IF(C4>69.9,"C","F",IF(OR(C4<69.9,C6>69.9,"B
+","F")))))))))

Thanking you all in advance for any advice you might be able to give.

Regards

Bill
Click to expand...

In general, it would be better to set this up as a table and use VLOOKUP to
determine the grade.

For example, to perform the conversion from the value in C4 to a grade, you can
set up a table like this (and name it GradeTbl):

0 F
70 C
75 B-
80 B
85 B+
90 A-
94 A
97 A+

and use this formula:

=VLOOKUP(C4,GradeTbl,2)

I'm not sure what you are doing at the end. In your formula, it will never get
evaluated. Perhaps something like:

=VLOOKUP(MAX(C4,C6),GradeTbl,2)

depending on what you do with the result in C6.

If you don't allow, on a retest, any grade greater than B+, then perhaps:

=VLOOKUP(MAX(C4,MIN(C6,85)),GradeTbl,2)

or if you average the failing grade and the retest:

=VLOOKUP(MAX(C4,AVERAGE(C6,C4)),GradeTbl,2)

etc.


--ron
 
What we are trying to do here is if you fail the first exam and then pass a rewrite the maximum grade you can receive is a B+
Click to expand...

Then my third suggestion should do the trick:

=VLOOKUP(MAX(C4,MIN(C6,85)),GradeTbl,2)

If you do the rewrite, but don't do as well as the initial test, which one
counts? My formula assumes the initial test would count.


--ron
 
Ron,

first of all thanks so much for your input.

As you assumed the mark would remain a "F". I haven't had time to give all this a try yet as it is for my sister that is trying to automate her classroom environment, but hope to have time to play with this tonight.

Thanks again

Regards

Bill
 
Back
Top