Quote:
Originally Posted by Sof
Hi All, I have another excel 2003 problem i'm trying to solve.
I was hoping to link a score system to a cells with text in them.
example if a score entered by a simple sum formula is between 1 & 20, i would like to display some text, if its between 21 & 50, a differernt text, and so on up to 6 scores.
it seams simple enough but can twork it out, I have tried nesting IF(AND( but it only return on one score or errors
Any help would be gratefully recieved
Sof
|
Let's say:
Raw Score is on A1
Text to display is on A2
Then your table looks like
A B C
4 1 10 Text 1
5 11 20 Text 2
6 21 30 Text 3
7 31 40 Text 4
8 41 50 Text 5
Your formula in A2 should be:
=IF(A1=A8,C8,IF(AND(A1=A7,A1<=B7),C7,IF(AND(A1= A6,A1<=B6),C6,IF(AND(A1=A5,A1<=B5),C5,C4))))
Remember: You should either start from the highest to lowest or vice-versa if you are going to use IF and AND combo.
If you want to avoid a confusing IF and AND function combo:
=VLOOKUP(A2,A4:C8,3,1)