Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
Look in the help index for VLOOKUPmake a table that can then be easily
changeduse that -- Don Guillett Microsoft MVP Excel SalesAid Software "Daisy77" wrote in message ... 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
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) -- Biff Microsoft Excel MVP "Daisy77" wrote in message ... 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
"Daisy77" wrote:
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 ----- "Daisy77" wrote: 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
Did none of the answers in your other thread help?
-- Best Regards, Luke M "Daisy77" wrote in message ... 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
Sorry for the extra post, my computer wasn't updating so I accidentally
re-sent it. I got my answer already. "Joe User" wrote: "Daisy77" wrote: 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 ----- "Daisy77" wrote: 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
"Daisy77" wrote:
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(B1514669,(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 ----- "Daisy77" wrote: 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
"Daisy77" wrote:
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" wrote: Sorry for the extra post, my computer wasn't updating so I accidentally re-sent it. I got my answer already. "Joe User" wrote: "Daisy77" wrote: 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 ----- "Daisy77" wrote: 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
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! "Luke M" wrote: Did none of the answers in your other thread help? -- Best Regards, Luke M "Daisy77" wrote in message ... 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy . |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement with 9 sequences.. problem
Thanks everyone for your replies.
I used the Vlookup function and It works!! "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Daisy77" wrote in message ... 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(B1514669,(B15-14669)*0.35,0)))))))) Please help!!! Thanks! Daisy . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statement with 9 sequences | Excel Discussion (Misc queries) | |||
If Statement with 9 sequences | Excel Discussion (Misc queries) | |||
Restricted value sequences in rows | Excel Worksheet Functions | |||
Save as: sequences? | Excel Discussion (Misc queries) | |||
paste sequences of different lengths | Excel Worksheet Functions |