IF statements with ranges in between

[chad87]

Hi guys, its been a while since iv done a complex if statement, was wondering if i could get some assistance.

kilometres................... statutory fractions

0-15,000..................... 0.2
15000-25000................ 0.2
25000-40000............... 0.14
>40000......................... 0.10

i need the cell to come up with the statutory figures

i started off with =if(<15000,"cell reference",if (i cant do the between range),if(>40000,"cellreference")

cheers

Chad

 

[Aladin Akyurek]

Hi guys, its been a while since iv done a complex if statement, was wondering if i could get some assistance.

kilometres................... statutory fractions

0-15,000..................... 0.2
15000-25000................ 0.2
25000-40000............... 0.14
>40000......................... 0.10

i need the cell to come up with the statutory figures

i started off with =if(<15000,"cell reference",if (i cant do the between range),if(>40000,"cellreference")

cheers

Chad

Let B2:C5 house the table above.
In A2:A5 enter the following numbers:

0
15000
25000
40000

Let E2 house a number like 27,000.

In F2 enter:

=LOOKUP(E2,$A$2:$A$5,$C$2:$C$2:$C$5)

Equivalently,

=INDEX($C$2:$C$5,MATCH(E2,$A$2:$A$5,1))

=VLOOKUP(E2,$A$2:$C$5,3,1)

 

[T. Valko]

Hi guys, its been a while since iv done a complex if statement, was wondering if i could get some assistance.

kilometres................... statutory fractions

0-15,000..................... 0.2
15000-25000................ 0.2
25000-40000............... 0.14
>40000......................... 0.10

i need the cell to come up with the statutory figures

i started off with =if(<15000,"cell reference",if (i cant do the between range),if(>40000,"cellreference")

cheers

Chad

Your ranges overlap.

If the kilometers was 25000 what result do you expect?

 

[chad87]

****, vlookup. I should've gone with that method instead of trying a if statement.

thanks you very much Aladin

T Valko, i see the issue, thanks for pointing it out. That table is actually a real table from Australian taxation office so i cant change it. i guess vlookup will have to do for now. Unless i change the range themselves.

thank you very much for your help.

 

[T. Valko]

In F2 enter:

=LOOKUP(E2,$A$2:$A$5,$C$2:$C$2:$C$5)

That result_vector doesn't look right.

Try it like this:

=LOOKUP(E2,$A$2:$C$5)

 

[T. Valko]

T Valko, i see the issue, thanks for pointing it out. That table is actually a real table from Australian taxation office so i cant change it. i guess vlookup will have to do for now. Unless i change the range themselves.

thank you very much for your help.

Good deal. Thanks for the feedback!

 

[Aladin Akyurek]

****, vlookup. I should've gone with that method instead of trying a if statement.

thanks you very much Aladin...

You are welcome.

LOOKUP has a typo though...

=LOOKUP(E2,$A$2:$A$5,$C$2:$C$5)

Thanks for providing feedback.

 

[T. Valko]

That result_vector doesn't look right.

Try it like this:

=LOOKUP(E2,$A$2:$C$5)

LOOKUP has a typo though...

=LOOKUP(E2,$A$2:$A$5,$C$2:$C$5)

Thanks for providing feedback.

You're welcome!