-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Multiple (more than 7) IF Formula
- Thread starter marihouse
- Start date
Zack Barresse
MrExcel MVP
- Joined
- Dec 9, 2003
- Messages
- 10,881
- Office Version
- 365
- 2010
- Platform
- Windows
- Mobile
- Web
no, only 7 nested. alternatives do exist, like using vlookup or hlookup and such, or the AND and OR functions.
Upvote
0
bjwillingham
Active Member
- Joined
- Jun 17, 2003
- Messages
- 287
You may also try defininin some of the conditions in a named formula.
Upvote
0
Iridium
Well-known Member
- Joined
- Jul 15, 2002
- Messages
- 2,831
marihouse said:
Unfortunately you can't get around the limit of 7 nested if statements.
Have a look at J-Walk: Alternatives to nested IF functions
HTH
Upvote
0
atmospheric
Well-known Member
- Joined
- Jul 16, 2002
- Messages
- 565
Just to go back a few threads in this discussion, Everyone agrees that the IF statement can only accomodate 7 IF's (Nested IF statement), but I have on occasion had to use IF for 9 or more conditions. Like so:
[Cell A1] = Nested IF statement containing 7 conditions but make the final "Condition if false", "" , (two apstrophies) in other words nothing.
Do the same to cell [A2] and cell [A3] if required. (Total of 21 IF's)
Then simply =A1&A2&A3 in the required cell to achieve your answer.
If the correct condition is not found in A1, the result is nothing, so adding nothing to cell A2 equals A2. Similarly if the result is not in A2, the answer is in A3. For correctness, make the final pair of apostrophies in A3 into a statment such as "Item not found"
It might be clumsy, but it works!
But, as my esteemed superiors say, there are easier ways!!!
[Cell A1] = Nested IF statement containing 7 conditions but make the final "Condition if false", "" , (two apstrophies) in other words nothing.
Do the same to cell [A2] and cell [A3] if required. (Total of 21 IF's)
Then simply =A1&A2&A3 in the required cell to achieve your answer.
If the correct condition is not found in A1, the result is nothing, so adding nothing to cell A2 equals A2. Similarly if the result is not in A2, the answer is in A3. For correctness, make the final pair of apostrophies in A3 into a statment such as "Item not found"
It might be clumsy, but it works!
But, as my esteemed superiors say, there are easier ways!!!
Upvote
0
Zack Barresse
MrExcel MVP
- Joined
- Dec 9, 2003
- Messages
- 10,881
- Office Version
- 365
- 2010
- Platform
- Windows
- Mobile
- Web
iridiums post will probably generate the best alternatives, as at the j-walk website. using the ampersand - concatenate - is useful to keep the functions in a single cell.
alternatively atmospheric's idea is great for sharing multiple if statements with various other cells. i.e. multiple other formulas only needing particular parts of the if statements. this would seem to be the most dynamic, to me.
good stuff though.
alternatively atmospheric's idea is great for sharing multiple if statements with various other cells. i.e. multiple other formulas only needing particular parts of the if statements. this would seem to be the most dynamic, to me.
good stuff though.
Upvote
0
jimrward
Well-known Member
- Joined
- Feb 24, 2003
- Messages
- 1,918
- Office Version
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- 2003 or older
- Platform
- Windows
hi can you post the multiple IF's you have so far, either in reply or using Colo's utility, and a broader description in your own words of what you are trying to achieve, as I have found on this site there are many ways to skin a cat, so to speak
Upvote
0
Similar threads
- Solved
