MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formula


Posted by DONALD ABER on April 08, 2001 12:48 PM

I HAVE A SPREADSHEET THAT I SET UP FOR NUMBER GRADES, SAY A1:A11, THEN THE EQUIVALENT LETTER GRADE IN B1:B11.
A PERSON WOULD BE REQUIRED TO ENTER A NUMERICAL VALUE IN D17 AND THE LETTER EQUIVALENT WOULD APPEAR IN D18.
Any idea on how to do a formula, to tie all these together.

Daaber40@hotmail.com


Posted by Aladin Akyurek on April 08, 2001 1:03 PM

Donald - Select the range A1:B11 and name the selected range GRADES via the Name Box or via the option Insert|Name|Define.

Name cell D17 Ngrade via the Name Box.

In D18 enter: =VLOOKUP(Ngrade,GRADES,2,1)

If you so desire, you can validate the number grade that is entered in D17. Meaning that you can restrict the allowable values. In order to do this, activate D17, activate the option Data|Validate, choose Custom for Allow on Settings tab, and enter the following formula:

=AND(D17>=0,D17<=100)

Aladin