how to assign a price to a range of numbers

[philesq]

I would like to know how to create a formula to take a range of numbers and assign a price. For instance, if the population of a county is between 5,000 and 9,999, the price would be $9.99; if the population of a county is between 10,000 and 19,999, the price would be $19.99.

I would greatly appreciate it if anyone can tell me how to do this.

[VBA Noob]

Hi,

Say A1 equals the value you want to check. Then enter this in B1

=IF(A1<5000,0,IF(A1<=9999,"$9.99",IF(A1<=19999,"$19.99","")))

This part of the formula "A1<5000,0" Will return 0 if under 5000.
and this part of the formula will return blank ,"") e.g "".

Can nest up to seven if statements

Hope it helps

VBA Noob

[philesq]

Thanks very much. I tried it and it works well, but I have 20 IF statements. Is there some way to do that?

I used

=IF(B40<2500,"$4.95",IF(B40<=4999,"$9.95",IF(B40<=9999,"$14.95",IF(B40<=19999,"$19.95",IF(B40<=29999,"$24.95",IF(B40<=49999,"$34.95",IF(B40<=99999,"$49.95",IF(B40<=149999,"$79.95",""))))))))

and tried to enter another IF statement, but as you said it only does 7.

[VBA Noob]

Hi,

Think your best using a lookup but the attached has some other ideas

http://www.j-walk.com/ss/excel/usertips/tip080.htm

VBA Noob