MS Excel 'IF' function 2

[jevans]

I'm trying to set-up and 'IF' formula to calculate a tax based on a IRS corporate tax schedule that must incle all of the applicable tax rates

To do so, I've built the following 'IF' statement but I can't get it to calculate the tax properly.

=IF(0<210000<=50000,(((210000-0)*0.15)+0),IF(50000<210000>75000,(((210000-50000)*0.25)+7500),IF(75000<210000>100000,(((210000-75000)*0.34)+13750),IF(100000<210000>335000,(((210000-100000)*0.39)+22250),IF(335000<210000>10000000,(((210000-335000)*0.34)+113900),IF(10000000<210000>15000000,(((210000-10000000)*0.35)+3400000),IF(15000000<210000>18333333,(((210000-15000000)*0.38)+5150000))))))))

The taxable income is $210,000, and the right answer is $65,150 but the 'IF' statement above is giving me $47,500 as the answer. What am I doing wrong? How do I separate the seven variable 'IF' statements so that they have no dependence on one another? If I just run the one 'IF' statement that directly applies to the $210,000 of taxable income ... &quot;IF(100000<210000>335000,(((210000-100000)*0.39)+22250)&quot; - the answer comes out fine.

 

[Goska]

I think I found the problem.

50000<210000>75000
To return true here, the input value (210000) must be greater than 75000.

Try
50000<210000<75000

Using 50000<210000>75000
always returns true, no matter what is in the middle.

 

[Goska]

Sorry, let me explain that a little more. Ignore the last message, I was thinking more in math terms than Excel terms.

50000<210000>75000
translates to if 21000 is greater than 50000 and if 21000 is greater than 75000, then true.

However, my version of Excel returns true no matter what 210000 is, even if I replace 210000 with negative numbers.

Try this, it works.

=if(and(0<210000,210000<=50000),True1,if(and(50000<210000,210000<=75000),True2,FalseEnd))

and so on......

If you want to be really trick, the use
=if(and(0<210000,210000<=50000),True1,if(210000<=75000,True2,FalseEnd))

since to get to to get to the second if(), it must be greater than 50000 anyway.

Hope this helps. If you still have problems, post again.

 

[jevans]

GOSKA - making the change you suggested, the answer comes back 'FALSE'. A number, 'the resulting tax amount should be coming back.

jevans

 

[jevans]

GOSKA - how should I have 'line wrapped' my original post? Now I'm off to try your second suggestion.

jevans

 

[Goska]

=IF(AND(0<P27,P27<=50000),(((P27-0)*0.15)+0),IF(P27<75000,(((P27-50000)*0.25)+7500),IF(P27<100000,(((P27-75000)*0.34)+13750),IF(P27<335000,(((P27-100000)*0.39)+22250),IF(P27<10000000,(((P27-335000)*0.34)+113900),IF(P27<15000000,(((P27-10000000)*0.35)+3400000),IF(P27<18333333,(((P27-15000000)*0.38)+5150000))))))))

is my exact code which seems to be working, assuming the input is in cell P27. I don't have a data set of correct answers, but the spot checking looks good.

As far as &quot;wrapping&quot; posts, I usually don't bother to do it. If you use a hard return, then it takes it into Excel as different cells in a row, which makes it really
hard for me to cut and paste and help you.

 

[DaleWatson123321]

Hi jevans,

Goska's option works well. Here's another... using the VLOOKUP function.

Formula: (&quot;ti&quot; refers to cell named &quot;ti&quot; (taxable income)
-------
=(ti-VLOOKUP(ti,taxtable,1))*VLOOKUP(ti,taxtable,2)+VLOOKUP(ti,taxtable,3)

Table (&quot;taxtable&quot; refers to range named &quot;taxtable&quot
-----
0 0.15 0
50,000 0.25 7,500
75,000 0.34 13,750
100,000 0.39 22,250
335,000 0.34 113,000
10,000,000 0.35 3,400,000
15,000,000 0.38 5,150,000
18,333,333 0.38 5,150,000

Your formula does not address what values are applicable at 18,333,333, so I used the same as for 15,000,000.

Hope this gives you more &quot;food for thought&quot; regarding options. One advantage of having a table, is that is can make it easier to make updates when necessary.

Regards, Dale Watson dwatson@bsi.gov.mb.ca

 

[jevans]

Goska...AWESOME, many thanks.

Your revised version works...smack-on! However, now I need to ask how do I add an 8th 'IF' statement to the same cell for taxable income of $18,333,333 or greater? Assuming the following - IF(P27>=18333333,((P27-18333333)*0.35)+6416667) - to be a correct 'IF' statement - how do I use it with Excel's limit of seven 'IF' statements per?

BTW, dwatson, thanks for your table idea. That my next venture to learn. Who knows, due to the Excel 7 'IF's per limit, 'next' could be sooner than later.

jevans

 

[jevans]

Goska...

Good news, I added the 8th 'IF' statement - IF(P27>=18333333,((P27-18333333)*0.35)+6416667) - for taxable income of $18,333,333 and greater - and it's working. Again, awesome!

However, this 'IF' statement - IF(I25<=150000,&quot;40,000.00 &quot;,IF(I25>150000,40000-((I25-150000)*0.25))) - is showing a negative number when it calculates a negative. What do I have to add to this 'IF' statement to show '0' when it calculates a negative?

Thanks

jevans