Different Ranges Different Answers

[neilbexton]

Hi All

I need to give different answers depending of what range of number appear.

Everything between 0-999 must return a 5
1000-1499 must return a 4
1500-1999 must return a 3
2000-2499 must return a 2
2500-2999 must return a 1
+3000 must return a 0

Can anyone help?

Thanks

[Norie]

Perhaps.

Please Login or Register  to view this content.

[martindwilson]

=LOOKUP(A1,{0,1000,1500,2000,2500,3000},{5,4,3,2,1,0})
if you dont want 5 if a1 is blank then
=IF(A1="","",LOOKUP(A1,{0,1000,1500,2000,2500,3000},{5,4,3,2,1,0}))