Thanks! Success with Converting Scores to Grades

[Arion]

Thanks. The final formula that worked was:

=VLOOKUP(AO5,{0,"F";60,"D-";63,"D";67,"D+";70,"C-";73,"C";77,"C+";80,"B-";83
,"B";87,"B+";90,"A-";93,"A";97,"A+"},2)


Does anyone know how to average weighted grades without counting blank
cells? For example;

AG5 is the average of daily work
AK5 is the quiz average
AN5 is the test average
A30 is the cumulative score =SUM(AG5,AK5,2*AN5)/4

However; if there are no quiz or test scores, then the cumulative score is
shown as #### or a #DIV/0 error. Is there a way to show the cumulative avg.
w/o all the values entered? In other words have a function that ignores AK5
until the cell has a value. I'd like to be able to show a student the grade
before major evaluations so I could say, "See what getting a 90 (or a zero)
on your quiz will do to your grade?"


All help and directions are greatly appreciated,
DKB

 

[Paul Corrado]

Arion,

AVERAGE("range") will ignore blanks.

However, the errors you are getting could be because all of the cells are
possibly either not blank, or not a entered as values. If you get an error
with this formula check back and I'm sure someone can help you sort it out.

PC

 

[Arion]

Paul Corrado,

=AVERAGE(AG15,AK15,AN15,AN15) works if each cell has a value, if I delete a
value then I get a #### and #DIV/0!. Each cell has an average function and
AO15 is the cumulative score of the three cells. Is there a way to have the
function ignore cells that do not have a numeric value while still
calculating a score for those cells that do?

Thanks for your help,
Arion

 

[Paul Corrado]

Please ignore my last post.

I just checked and verified that AVERAGE is ignoring everything but
numerical values in my computer. (XL2000)

#Div/0 will appear if there are no values in any of the cells. I assumed
that would not happen given that you are averaging test scores. Because of
that assumption I didn't provide a formula that would eliminate that error.
However, that error can be eliminated with

=IF(sum(AG15,AK15,AN15,AN15)=0,0,AVERAGE(AG15,AK15,AN15,AN15))

It's not clear though why you would get an ##### error, unless it is just
because the cell is not wide enough.

Hopefully someone else will have insight into this.

PC