Assigning a value to a range of numbers

[whacknal]

Trying to find a formula that can help me give a value to different range of numbers.

The numbers will range between 600 to -600 and will change constantly.

400 to 600 =10
250 to 399=7.5
100 to 249 =5
99 to -99 =0
-100 to -249 = -5
-200 to -399 = -7.5
-400 to -600 = -10

so if a1=140 b2=5
if a1=-415 b2=-10
if a1=50 b2 =0

Thanks in advance

[leelnich]

Hi whacknal- Try this:
=CHOOSE(MATCH(A1,{-600,-399,-249,-99,100,250,400}),-10,-7.5,-5,0,5,7.5,10)

BTW, not that it matters, but in your list of ranges, I think -200 should be -250.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[whacknal]

You are a superstar. Continuously astounded by the smarts of people on here. Thank you very much!!

And yes I did mean -250.....lol

[leelnich]

Happy to help, thank you for the rep!

[sktneer]

Instead of hard coding the lookup values, set up a lookup table as shown in the image so that you can change them when required and try this formula...

In B1

Please Login or Register  to view this content.

and copy it down.
Lookup.JPG

[leelnich]

Hi sktneer- Your negative lookup numbers are reversed. You need to switch columns E and F in rows 1-3 so the largest number is always in F.
BTW, I considered a look-up. If the list were any longer, I too would have taken that approach.

[sktneer]

Good catch leelnich!
Thanks for pointing that out. Much appreciated.

[leelnich]

Also, SUMIFS isn't very efficient here. In this case, it must do 8-14 comparisons AND set up a sum! VLOOKUP (using approximate match) does a binary search, requiring a maximum of 4 comparisons.
In B1:
=VLOOKUP(A1,$E$1:$F$7,2)

Row\Col	A	B	C	D	E	F
1	331	7.5			-600	-10
2	-418	-10			-399	-7.5
3	113	5			-249	-5
4	-186	-5			-99	0
5	-58	0			100	5
6	-151	-5			250	7.5
7	-193	-5			400	10

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[sktneer]

That's all about personal preference. If you have a lookup table with lower and upper boundaries, it looks more readable. Isn't it?

[leelnich]

Yes, it is, though the more numbers you enter, the bigger the chance of making a mistake, as demonstrated in post #1 (-200 should have been -250). Sorry if I got a little strident, the avoidance of slow calculation, which can lead to loss of user focus, has been a HUGE issue with several large projects lately. Take care-Lee