# ARRAY OR IF STATEMENT

Posted by martha on September 25, 2001 7:32 PM

Hello everyone,
This will be easy. I am setting up a formula for calculating a grade to the regular point system. A = 90 - 100, B = 80 - 89, C = 70 - 79....etc. Do I use an array or if statement and what would that be. I left my handy excel book at work so I am brainless at this moment.

Thank you,

Martha

Posted by Richard S on September 25, 2001 8:37 PM

I'm not sure of your grading system, but of you set up a table with the change point of each grade in ascending order, with the grade it changes to at that point in the next column, you could use VLOOKUP, eg.
70 C
80 B
90 C

The 70 is in cell A2, C is in cell B4, your score is in cell D1. the formula would be =VLOOKUP(D1,\$A\$2:\$B\$4,2).

Make sense?

Richard

Posted by MARTHA on September 25, 2001 8:57 PM

Thank you for the suggestion. I thought about a lookup table, but I am sure there is an easier way. If I dont find out than I will use your suggestion.

Thanx again.
Martha

Posted by Aladin Akyurek on September 25, 2001 9:29 PM

With VLOOKUP

Following up Richard's suggestion:

=VLOOKUP(A1,{0,"F";60,"D";70,"C-";75,"C";77,"C+";80,"B-";85,"B";87,"B+";90,"A-";94,"A";98,"A+"},2)

Adapt the constant array (which is an equivalent of a 2-column table) to your situation if needed.