If statement with 9 sequences.. problem

[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.

This is my current formula
=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))))))))

Please help!!!

Thanks!
Daisy

 

[Don Guillett]

Look in the help index for VLOOKUP>make a table that can then be easily
changed>use that

 

[T. Valko]

If B4<529,0. I want it to return

a value of zero if cell B4 is < 529.
This is my current formula:
=IF(B15<943,(B15-529)*0.1,...

You say cell B4 but your formula says cell B15.

Create this 3 column 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...14669...0.35

Assume that table is in the range B1825

This formula will do what your *current* nested IF formula is doing:

=(B15-LOOKUP(B15,B18:C25))*LOOKUP(B15,B1825)

Ok, so it sounds like you want to tweak this for the condition <529 = 0.

Just add a new line to the table:

0...0...0
529...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

Now the table range is B1826

The formula is the same, just uses the new table range:

=(B15-LOOKUP(B15,B18:C26))*LOOKUP(B15,B1826)

 

[Joe User]

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.

Generally, see alternative methods described at
http://www.mcgimpsey.com/excel/variablerate.html.

If you still need specific help, post a follow-up to this thread.

(Not good to start new threads for what amounts to a continuation of an
existing discussion.)


----- original message -----

 

[Luke M]

Did none of the answers in your other thread help?

 

[Daisy77]

Sorry for the extra post, my computer wasn't updating so I accidentally
re-sent it.
I got my answer already.

 

[Joe User]

This is my current formula:
=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))))))))

I just realized that the your tax table structure is unlike any that I have
seen before. For my edification, please identify the government and purpose
this tax structure.

Obviously, you have some mistakes. The formula above would return negative
tax for any amount less than 529. I wonder if you have other mistakes as
well.

If not, then the following might be the best way to accomplish the same thing:

=MAX(0, (B15-VLOOKUP(A10,$X$1:$Z$8,3))
*VLOOKUP(B51,$X$1:$Z$8,2))

where X1:X8 contains 0, 943, 2914, 3618, 4772, 5580, 8347, 14670; Y1:Y8
contains 10%, 15%, 25%, 27%, 25%, 28%, 33%, 35%; Z1 contains 529, Z2 contains
=X2-1, and copy Z2 down through Z8.

(Caveat: look for any typos of mine.)

Is there some reason why you cannot use VLOOKUP?


----- original message -----

 

[Joe User]

I got my answer already.

I'm glad you think you did.

I am suspicious of the tax table structure that you present. That tax on
950 is less than the tax on 600. There are many other examples.


----- original message -----

 

[Daisy77]

Luke,
my computer was delaying so I accidentally posted this more than once. I was
unable to delete the duplicate postings.
Your suggestion worked perfectly.
Thank you very much!

 

[Daisy77]

Thanks everyone for your replies.
I used the Vlookup function and It works!!