If nested function

[William (Bill) C. Earl]

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

 

[Ron Rosenfeld]

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

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

 

[Ron Rosenfeld]

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+

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

 

[William (Bill) C. Earl]

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