Excel cell conversion problem

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.