| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Statement with 9 sequences
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
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. "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. Please help!!! Thanks! Daisy |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
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" 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. Please help!!! Thanks! Daisy |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
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(B1514669,(B15-14669)*0.35,0)))))))) "Mike H" wrote: 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. "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. Please help!!! Thanks! Daisy |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
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(B1514669,(B15-14669)*0.35,0)))))))) Thanks! "מיכאל (מיקי) אבידן" wrote: 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" 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. Please help!!! Thanks! Daisy |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
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" wrote in message ... 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(B1514669,(B15-14669)*0.35,0)))))))) "Mike H" wrote: 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. "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. Please help!!! Thanks! Daisy |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
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,TRU E)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Daisy77" wrote: 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(B1514669,(B15-14669)*0.35,0)))))))) "Mike H" wrote: 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. "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. Please help!!! Thanks! Daisy |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
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. "Mike H" wrote: 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,TRU E)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Daisy77" wrote: 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(B1514669,(B15-14669)*0.35,0)))))))) "Mike H" wrote: 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. "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. Please help!!! Thanks! Daisy |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
Thank you, it works perfectly :)
"Mike H" wrote: 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. "Mike H" wrote: 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,TRU E)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Daisy77" wrote: 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(B1514669,(B15-14669)*0.35,0)))))))) "Mike H" wrote: 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. "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. Please help!!! Thanks! Daisy |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Statement with 9 sequences
Thanks, it works great!
"Luke M" wrote: 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" wrote in message ... 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(B1514669,(B15-14669)*0.35,0)))))))) "Mike H" wrote: 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. "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. Please help!!! Thanks! Daisy . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| running number sequences | Excel Discussion (Misc queries) | |||
| Help needed on generating sequences | New Users to Excel | |||
| Restricted value sequences in rows | Excel Worksheet Functions | |||
| Save as: sequences? | Excel Discussion (Misc queries) | |||
| paste sequences of different lengths | Excel Worksheet Functions | |||
Linear Mode
