If Statement with 9 sequences

[Daisy77]

Hello everybody,

I currently have an IF statement w/8 sequences and it is working properly.
I'm doing this for tax purposes. When I try to add one more sequence, it
doesn't work. basically I just want to say If B4<529,0. I want it to return
a value of zero if cell B4 is < 529. Please help!!!

Thanks!
Daisy

 

[Mike H]

Daisy,

maybe we can simplify your nested if, post your formula and the
addition/change you want.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[מיכ×ל (מיקי) ×בידן]

Usually - with over than 7 nested ifs - you will use a small helper table
from which you will return the appropriate value with the VLOOKUP function.
Excel 2007/2010 allows up to 64 nested ifs but it will be hard to
read/understand.
It is difficult to propose a more direct solution without having all the
details.
Micky

 

[Daisy77]

sorry this is the formula i have
=IF(B15<943,(B15-529)*0.1,IF(B15<2914,(B15-942)*0.15,IF(B15<3618,(B15-2913)*0.25,IF(B15<4772,(B15-3617)*0.27,IF(B15<5580,(B15-4771)*0.25,IF(B15<8347,(B15-5579)*0.28,IF(B15<14670,(B15-8346)*0.33,IF(B15>14669,(B15-14669)*0.35,0))))))))

 

[Daisy77]

this is the current formula i have
=IF(B15<943,(B15-529)*0.1,IF(B15<2914,(B15-942)*0.15,IF(B15<3618,(B15-2913)*0.25,IF(B15<4772,(B15-3617)*0.27,IF(B15<5580,(B15-4771)*0.25,IF(B15<8347,(B15-5579)*0.28,IF(B15<14670,(B15-8346)*0.33,IF(B15>14669,(B15-14669)*0.35,0))))))))

Thanks!

 

[Luke M]

This will work better if you build a lookup table. Lets say its in A2:C9,
looks like:

0 529 .1
943 942 .15
2914 2913 .25
3618 3618 .27
4772 4771 .25
5580 5579 .28
8347 8346 .33
14670 14669 .35
etc...

Your formula then becomes:
=(B15-LOOKUP(B15,A2:B9))*LOOKUP(B15,A2:A9,C2:C9)

This approach has the advantage in that its easier to change your values,
audit formulas, and you can have thousands of possibile choices.

 

[Mike H]

Daisy,

Build a lookup table

0 529 0.1
943 942 0.15
2914 2913 0.25
3618 3617 0.27
4772 4771 0.25
5580 5579 0.28
8347 8346 0.33
14670 8346 0.35


I put mine in G1 to I8. And then this formula

=IF(B4=0,0,(B15-VLOOKUP(B15,G1:H8,2,TRUE))*VLOOKUP(B15,G1:I8,3,TRUE))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

oops.

error in my table, use this

0 529 0.1
943 942 0.15
2914 2913 0.25
3618 3617 0.27
4772 4771 0.25
5580 5579 0.28
8347 8346 0.33
14670 14669 0.35

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Daisy77]

Thanks, it works great!

This will work better if you build a lookup table. Lets say its in A2:C9,
looks like:

0 529 .1
943 942 .15
2914 2913 .25
3618 3618 .27
4772 4771 .25
5580 5579 .28
8347 8346 .33
14670 14669 .35
etc...

Your formula then becomes:
=(B15-LOOKUP(B15,A2:B9))*LOOKUP(B15,A2:A9,C2:C9)

This approach has the advantage in that its easier to change your values,
audit formulas, and you can have thousands of possibile choices.

--
Best Regards,

Luke M



.

 

[Daisy77]

Thank you, it works perfectly

oops.

error in my table, use this

0 529 0.1
943 942 0.15
2914 2913 0.25
3618 3617 0.27
4772 4771 0.25
5580 5579 0.28
8347 8346 0.33
14670 14669 0.35

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.