U
unouwanme
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?
error, but is ok when i delete 2...
Anyone know?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
is there a limt to the amount of if statements you use in just 1 c
S
scottfoxall
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.
but using the VBA is a better solution.
D
Dave F
The limit is 7 nested IF functions.
N
Niek Otten
http://www.cpearson.com/excel/nested.htm
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
|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?
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
|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?
P
Pete_UK
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
of your formula and a more fullsome description of what you want to
achieve, and someone will be able to help you.
Pete
U
unouwanme
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,if(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(b2=10,a2+$P$11,""))))))))))
Again thank ou for your replies
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,if(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(b2=10,a2+$P$11,""))))))))))
Again thank ou for your replies
P
Pete_UK
Try this:
=IF(OR(B2<1,B2>10),"",A2+INDIRECT("$P$"&(B2+1)))
Hope this helps.
Pete
=IF(OR(B2<1,B2>10),"",A2+INDIRECT("$P$"&(B2+1)))
Hope this helps.
Pete
U
unouwanme
Yea, that works great thanks.....how do you know things like that??
P
Pete_UK
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
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