Excel help pls

[Sonic_Vrs]

Hi all.

I know there is something to help me here.

Is there a function that will read a cell, and given the value will replace with a certain text.

ie if cell contains number between 240 and 270 replace with an A, if between 210 and 240 replace with B etc.

Help!

[Hannibal]

I assume you would have something like a column of numbers and you would want the letter to appear in the column next to this? I'd probably do this with an IF formula although there is probably a better way.

[Sonic_Vrs]

I assume you would have something like a column of numbers and you would want the letter to appear in the column next to this? I'd probably do this with an IF formula although there is probably a better way.

yes. column of different numbers, and letter (according to where number is in range) would come up next to this. What If would you do?

[Hannibal]

Say you wanted letters A-D I'd have say

IF(Cell<200,A,IF(Cell<250,B,IF(Cell<300,C,D)))

Which basically says if it's less than 200, then A, if not, then if it's less than 250, B and if it's not, and it's less than 300 then it's C and if it's nt less than 30,then it's D. This would get very long winded for say A-Z hence I say there is probably a better way!

[Sonic_Vrs]

perfect, thats what i remember...

Only have to do GCSE grades, so only 9 so not too long.

Thanks again

[Hannibal]

You're welcome.

If anybody does know of a better way I'd still be interested to hear it.

EDIT:

Just thought, if it was for grades, and you had whole numbers as the marks then you could have a list of say 1-100, or 1-400 as appropriate and then put the grades against each result. ie 90-100 would be A*, 80-89 would be A etc. Then do a VLOOKUP into that table against you list of grades. You could also use the ROUND formula to ensure your grades were whole numbers if they weren't already.

Edited 4 October, 2010 by Hannibal

[JLneonhug]

I think there is another way iirc, its to do with conditions. Its done with options/pop up boxes and not writing functions. You can change colour of font and background square as-well depending on condition..

Too long ago.. sorry don't remember, and I have 2007 now so have to relearn anything again :|

Edited 4 October, 2010 by JLneonhug

[Hannibal]

I think there is another way iirc, its to do with conditions. Its done with options/pop up boxes and not writing functions. You can change colour of font and background square as-well depending on condition..

Too long ago.. sorry don't remember, and I have 2007 now so have to relearn anything again :|

That would be using conditional formatting, but I'm not sure if that coudl deliver the correct letter output or only change the format of the cells.

[Sonic_Vrs]

You're welcome.

If anybody does know of a better way I'd still be interested to hear it.

EDIT:

Just thought, if it was for grades, and you had whole numbers as the marks then you could have a list of say 1-100, or 1-400 as appropriate and then put the grades against each result. ie 90-100 would be A*, 80-89 would be A etc. Then do a VLOOKUP into that table against you list of grades. You could also use the ROUND formula to ensure your grades were whole numbers if they weren't already.

Vlookup would indeed do it, but it was too long ago and cant remember how to set up. The first option you suggested will suffice...

[Robshaw]

There are many ways that you could do this - If you need a hand, please do send me a PM. I'm more than willing to help.

To save time - IF statement example:

http://en.allexperts.com/q/Excel-1059/cell-A1-string-value.htm

VLOOKUP example:

http://www.mrexcel.com/tip136.shtml

Conditional formatting example:

http://excel.bigresource.com/Conditional-Formatting-Grade-Letters-JNUaqzeA.html#sAnzAEns

I hope this helps,

Rob.