Excel cell conversion problem

J

Jane

To convert numbers into GCSE grades I need to use a formula that will read
down a column and if the cell content is between 1-7 to insert the letter U,
if the cell content is between 8-11 to insert the letter G, if the cell
content is between 12-15 to insert the letter F,etc

Any ideas?
 
M

Mike

Paste in an empty cpolumn and drag down. I haven't set up for checks outside
of the range of marks you specified i.e. 0 or (say) 99 so if this is a
problem re-post

=IF(A1<=7,"U",IF(A1<=11,"G","F"))

Mike
 
M

Mike

Jane,

Sorry, I didn't like it wothout the error checks so hers a version that
checks for numbers in your range and for numeric input only.

=IF(ISNUMBER(A1),IF(A1<=7,"U",IF(A1<=11,"G",IF(A1<=15,"F","Value out of
range"))),"Value not numeric")

Mike
 
J

Joel

Mike's solution if good if you have only a small number of ranges. Because
you have etc I don't know how big your table really is. for large tabels the
best way is to have a table of every number and letter(see table below). the
formula to use would be VLOOKUP(). The formula become very simple to enter
and get working. It may take more time to enter the table but it save time
by eliminating debuging complex formulas.

1 U
2 U
3 U
4 U
5 U
6 U
7 U
8 G
9 G
10 G
11 G
12 F
13 F
14 F
15 F
16
17
18
19
 
J

Jane

many thanks for your prompt reply. Toppers came up with
=LOOKUP(D10,{1,8,12,16,20,24,28,32,36},{"U","G","F","E","D","C","B","A","A*"})

which worked.
 
J

Jane

Many thanks Mike. Has a reply from Toppers who told me to try
=LOOKUP(D10,{1,8,12,16,20,24,28,32,36},{"U","G","F","E","D","C","B","A","A*"}) which worked a treat so thanks for all your help.
 
Top