If statement with 9 sequences.. problem

D

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
 
D

Don Guillett

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

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 B18:D25

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

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

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 B18:D26

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

=(B15-LOOKUP(B15,B18:C26))*LOOKUP(B15,B18:D26)
 
J

Joe User

Daisy77 said:
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 -----
 
D

Daisy77

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

Joe User

Daisy77 said:
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 -----
 
J

Joe User

Daisy77 said:
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 -----
 
D

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top