Can you create an if statement for me?

If a4=<75,001 fee=2.00
if a4=75,001 but <125001 fee=1.75
if a4=125001 but<250001 fee=1.50
if a4=250001 but<1000001 fee=1.25
if a4=1000001 fee=1.00

Hi

One way, without IF statements
=1+(A4<1000001)*0.25+(A4<250001)*0.25+(A4<125001)* 0.25+(A4<75001)*0.25
Regards

Roger Govier


mmr wrote:
Can you create an if statement for me?

If a4=<75,001 fee=2.00
if a4=75,001 but <125001 fee=1.75
if a4=125001 but<250001 fee=1.50
if a4=250001 but<1000001 fee=1.25
if a4=1000001 fee=1.00

=2-(0.25*(MATCH(A4,{0,75002,125002,250002,1000001},1)-1))

--
HTH

Bob Phillips

"mmr" wrote in message
...
Can you create an if statement for me?

If a4=<75,001 fee=2.00
if a4=75,001 but <125001 fee=1.75
if a4=125001 but<250001 fee=1.50
if a4=250001 but<1000001 fee=1.25
if a4=1000001 fee=1.00

Hi,

Try these:

=LOOKUP(A4,{0,75000,125000,250000,100000},{2,1.75, 1.5,1.25,1})
=INDEX({2,1.75,1.5,1.25,1},MATCH(A4,{0,75000,12500 0,250000,100000}))
=CHOOSE(MATCH(A4,{0,75000,125000,250000,100000}),2 ,1.75,1.5,1.25,1)
=VLOOKUP(A4,{0,2;75000,1.75;125000,1.5;250000,1.25 ;100000,1},2)

the fixed arrays can also be replaced by references to ranges, e.g. if you
had the following table in range [C1:D5]
[C] [D]
------------------
0 2

75000 1.75

125000 1.5

250000 1.25

100000 1


then:

=LOOKUP(A4,C1:C5,D1:D5)
=INDEX(D1:D5,MATCH(A4,C1:C5))
=CHOOSE(MATCH(A4,C1:C5),2,1.75,1.5,1.25,1)
=VLOOKUP(A4,C1:D5,2)

Regards,
KL


"mmr" wrote in message
...
Can you create an if statement for me?

If a4=<75,001 fee=2.00
if a4=75,001 but <125001 fee=1.75
if a4=125001 but<250001 fee=1.50
if a4=250001 but<1000001 fee=1.25
if a4=1000001 fee=1.00