Row of numbers - need to average w/ grade letter

**couger77** · 02-28-2007, 04:33 PM

I have a row of numbers:

2.0 2.0 3.0 4.0 3.0

I need to average this row and then assign a grade letter according to the result. I have a table that lists the Grade and Avg in two columns to accomplish the latter. (the table is named "SCALE")

Thanks

**BigBas** · 02-28-2007, 05:29 PM

can you please tell what each number translates to in letters? ie, is a 4.0 an A, etc

**couger77** · 02-28-2007, 05:33 PM

0.00 F
1.00 D
1.67 C-
2.00 C
2.33 C+
2.67 B-
3.00 B-
3.33 B+
3.67 A-
4.00 A-

**couger77** · 02-28-2007, 05:34 PM

It also needs to ignore #N/A in any given cell.

Thanks!

**BigBas** · 02-28-2007, 05:42 PM

Assuming your information is running in row A, columns 1-5:

First, for the average, enter this formula into Cell F1:

=AVERAGE(A1:E1)

Then, in cell G1, enter this formula:

=LOOKUP(F1,{0,1,1.67,2,2.33,2.67,3,3.33,3.67,4},{"F","D","C-","C","C+","B-","B","B+","A-","A+"})

Let me know if that works

EDITED TO ADD:
I didnt see your request for it to ignore #N/A. Use this formula instead:

=IF(ISERROR(F1),"",LOOKUP(F1,{0,1,1.67,2,2.33,2.67,3,3.33,3.67,4},{"F","D","C-","C","C+","B-","B","B+","A-","A+"}))