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.
Score Grade
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.
Aladin
