| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
is there a limt to the amount of if statements you use in just 1 c
I am trying to use 10 if statements in 1 cell, but keeps coming up with an
error, but is ok when i delete 2... Anyone know? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just 1 c
The max is 7 IF statments in 1 cell. You can a pyramid structure to get more
but using the VBA is a better solution. "unouwanme" wrote: I am trying to use 10 if statements in 1 cell, but keeps coming up with an error, but is ok when i delete 2... Anyone know? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just 1 c
The limit is 7 nested IF functions.
-- Brevity is the soul of wit. "unouwanme" wrote: I am trying to use 10 if statements in 1 cell, but keeps coming up with an error, but is ok when i delete 2... Anyone know? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just 1 c
http://www.cpearson.com/excel/nested.htm
-- Kind regards, Niek Otten Microsoft MVP - Excel "unouwanme" wrote in message ... |I am trying to use 10 if statements in 1 cell, but keeps coming up with an | error, but is ok when i delete 2... | | Anyone know? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just 1 c
There are many ways round the limit of 7 nested IFs - post an example
of your formula and a more fullsome description of what you want to achieve, and someone will be able to help you. Pete unouwanme wrote: I am trying to use 10 if statements in 1 cell, but keeps coming up with an error, but is ok when i delete 2... Anyone know? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just 1 c
Thanks for all of your help/comments.
Basically all i am wanting to do is: If the number in this cell is 1 add this to this, if 2 add this to this etc =If(B2=1,A2+$P$2,if(b2=2,a2+$P$3,if(b2=3,a2+$P$4,i f(b2=4,a2+$P$5,if(b2=5,a2+$P$6,if(b2=6,a2+$P$7,if( b2=7,a2+$P$8,if(b2=8,a2+$P$9,if(b2=9,a2+$P$10,if(b 2=10,a2+$P$11,"")))))))))) Again thank ou for your replies |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just 1 c
Try this:
=IF(OR(B2<1,B210),"",A2+INDIRECT("$P$"&(B2+1))) Hope this helps. Pete unouwanme wrote: Thanks for all of your help/comments. Basically all i am wanting to do is: If the number in this cell is 1 add this to this, if 2 add this to this etc =If(B2=1,A2+$P$2,if(b2=2,a2+$P$3,if(b2=3,a2+$P$4,i f(b2=4,a2+$P$5,if(b2=5,a2+$P$6,if(b2=6,a2+$P$7,if( b2=7,a2+$P$8,if(b2=8,a2+$P$9,if(b2=9,a2+$P$10,if(b 2=10,a2+$P$11,"")))))))))) Again thank ou for your replies |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just
Yea, that works great thanks.....how do you know things like that??
"Pete_UK" wrote: Try this: =IF(OR(B2<1,B210),"",A2+INDIRECT("$P$"&(B2+1))) Hope this helps. Pete unouwanme wrote: Thanks for all of your help/comments. Basically all i am wanting to do is: If the number in this cell is 1 add this to this, if 2 add this to this etc =If(B2=1,A2+$P$2,if(b2=2,a2+$P$3,if(b2=3,a2+$P$4,i f(b2=4,a2+$P$5,if(b2=5,a2+$P$6,if(b2=6,a2+$P$7,if( b2=7,a2+$P$8,if(b2=8,a2+$P$9,if(b2=9,a2+$P$10,if(b 2=10,a2+$P$11,"")))))))))) Again thank ou for your replies |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a limt to the amount of if statements you use in just
Thanks for feeding back - glad it worked for you.
I could see by examining what you wanted to do that the only variable was the row number for column P and that this was related to the value in B2. The INDIRECT function allows you to use a calculated cell reference, so it was then obvious how to convert your formula. Often, you would use a lookup table to replace multiple IFs, but that wouldn't work in this case as it was the cell reference you wanted to change rather than just a value. Pete unouwanme wrote: Yea, that works great thanks.....how do you know things like that?? "Pete_UK" wrote: Try this: =IF(OR(B2<1,B210),"",A2+INDIRECT("$P$"&(B2+1))) Hope this helps. Pete unouwanme wrote: Thanks for all of your help/comments. Basically all i am wanting to do is: If the number in this cell is 1 add this to this, if 2 add this to this etc =If(B2=1,A2+$P$2,if(b2=2,a2+$P$3,if(b2=3,a2+$P$4,i f(b2=4,a2+$P$5,if(b2=5,a2+$P$6,if(b2=6,a2+$P$7,if( b2=7,a2+$P$8,if(b2=8,a2+$P$9,if(b2=9,a2+$P$10,if(b 2=10,a2+$P$11,"")))))))))) Again thank ou for your replies |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for amount owing subtract amount paid | Excel Worksheet Functions | |||
| Linking two IF statements together | Excel Discussion (Misc queries) | |||
| formula to calculate a benefit amount | Excel Discussion (Misc queries) | |||
| How do I calculate Amount of Sales Tax from Total Amount? | Excel Worksheet Functions | |||
| Loan Amortization Template - Amount of Final Payment | Excel Discussion (Misc queries) | |||
Hybrid Mode
