=IF formula

[gstone234]

TTL. BILLED RANGE #1 RANGE #2
$200.00 $0-$2500 $0-$2500
$700.00 $0-$2500 $0-$2500
$1,000.00 $0-$2500 $0-$2500
$1,200.00 $0-$2500 $0-$2500
$2,000.00 $0-$2500 $0-$2500
$2,500.00 $0-$2500 $0-$2500

$2,600.00 $2600-$5000 $2600-$5000
$2,900.00 $2600-$5000 $2600-$5000
$3,500.00 $2600-$5000 $2600-$5000
$4,200.00 $2600-$5000 $2600-$5000
$5,000.00 $2600-$5000 $2600-$5000

$6,000.00 $6000-$10000 $2600-$5000
$7,800.00 $6000-$10000 $2600-$5000
$8,800.00 $6000-$10000 $2600-$5000
$9,000.00 $6000-$10000 $2600-$5000
$10,000.00 $6000-$10000 $2600-$5000

$12,000.00 $12000-$500000 $2600-$5000
$14,000.00 $12000-$500000 $2600-$5000
$20,000.00 $12000-$500000 $2600-$5000
$38,000.00 $12000-$500000 $2600-$5000
$50,000.00 $12000-$500000 $2600-$5000


=IF(A27<2501,"$0-$2500",IF(A27>2599,"$2600-$5000",IF(A27>5999,"$6000-$10000",IF(A27>11999,"$12000-$50000"))))

Why doesn't this formula work for the figures in the RANGE #2 column for the
$6000-$10000 amounts in RANGE #1. It works for all the amounts above the
$6000-$10000

 

[Bernard Liengme]

=IF(A27<2501,"$0-$2500",IF(A27>2599,"$2600-$5000",IF(A27>5999,"$6000-$10000",IF(A27>11999,"$12000-$50000"))))

Suppose A27 is 6000. Then A27>2599 is true a you will get $2600-$5000 which
is incorrect

Use the logic of the first part A27<2501

=IF(A27<2501,"$0-$2500",IF(A27<5001,"$2600-$5000",IF(A27<10001,"$6000-$10000",IF(A27<
50001,"$12000-$50000", "too big"))))

Check you logic: what do you want if A7 =1100 ?
best wishes

 

[gstone234]

Bernard, the formula worked fine. Thanks for your help.

 

[Shane Devenshire]

Hi,

Just a few comments.

If A27 is 2500.99 your result will be 0-2500, is that what you want?

You appear to be missing some values between 2501-2599, and between
5001-5999 and 10001-11999. Is this intentional.

Another alternative which will work if you want to include the missing value
as I show above would be VLOOKUP. Make a table like the following in say
D1:F4:

0 $0-$2500
2600 $2600-$5000
6000 $6000-$100000
12000 $12000-$50000

The use a formula like

=VLOOKUP(A27,D1:F4,2,True)

The advantage in using VLOOKUP is that in 2003 you are limited to 7 levels
deep of nesting, but VLOOKUP has no limit. And even when you can use more
levels of nesting, 2007, VLOOKUP is a lot shorter.