Nested if statement

[rayrickson]

hi all and thanks for your time!

i am having trouble getting this formula to work. is it true you can only have 7 nested if statements? here is the formula

=IF(or(AND(C31=2,C30=1,C32=1),31,IF(AND(C31=1,C30=2,C32=2),1,IF(AND(C31=2,C30=2,C29=1,C32=1),32,IF(AND(C31=1,C30=1,C29=2,C32=2),2,IF(AND(C31=2,C30=2,C29=2,C28=1,C32=1),33,IF(AND(C31=1,C30=1,C29=1,C28=2,C32=2),3,IF(AND(C31=2,C30=2,C29=2,c28=2,C27=1,C32=1),34,IF(AND(C31=1,C30=1,C29=1,c28=1,C27=2,C32=2),4,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,C26=1,C32=1),35,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,C26=2,C32=2),5,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,C25=1,C32=1),36,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,C25=2,C32=2),6,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,C24=1,C32=1),37,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,C24=2,C32=2),7,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,C23=1,C32=1),38,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,C23=2,C32=2),8,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,C22=1,C32=1),39,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,C22=2,C32=2),9,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,C21=1,C32=1),40,IF(AND(C31=1,C30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,C21=2,C32=2),10,IF(AND(C31=2,C30=2,C29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,C20=1,C32=1),41,IF(AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,C20=2,C32=2),11,IF(AND(C31=2,C30=2,29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,c20=2,C19=1,C32=1),42,IF(AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,c20=1,C19=2,C32=2),12,IF(AND(C31=2,C30=2,29=2,c28=2,c27=2,c26=2,c25=2,c24=2,c23=2,c22=2,c21=2,c20=2,C19=2,C32=1),43,IF(AND(C31=1,30=1,C29=1,c28=1,c27=1,c26=1,c25=1,c24=1,c23=1,c22=1,c21=1,c20=1,C19=1,C32=2),13,””))))))))))))))))

any help or thoughts would be appreciated. i tried switching it to an "ifs" statement but evidently you need a subscription to office 365? thanks in advance

 

[Gerald Higgins]

Re: help with nested if statement

Hi, welcome to the board.

There used to be quite a low limit to the number of nested IF statements (I think it was 6) in older versions of Excel, but newer versions allow many more.

I personally don't have access to the IFS function but I doubt it is appropriate here.

I'll take a closer look at your formula, I suspect a completely different approach may be called for.

 

[sheetspread]

Re: help with nested if statement

Isn't a lookup table better than writing out all these conditions?

 

[Gerald Higgins]

Re: help with nested if statement

Deleted

 

[Marcílio_Lobão]

Re: help with nested if statement

rayrickson, Good afternoon.

"...is it true you can only have 7 nested if statements?..."
If you are getting this message it is because your version of excel is 2003.

In this version only 7 levels of IF function are possible.
In newer versions up to 64 IF function levels are possible.

"...i tried switching it to an "ifs" statement but evidently you need a subscription to office 365?..."
You're absolutely right.


However, there are always many more efficient functions that replace these possible IF nested functions.

Explain to us exactly what you need.

Save an example of your spreadsheet on a free website, www.sendspace.com or another and place the link here for download.

Show the data you have and what the expected result will be.
I'm sure that your answer will come more accurately and quickly.

Have a nice day!

 

[Gerald Higgins]

Re: help with nested if statement

Yes, as Sheetspread says, a lookup table is probably a better approach.

Even if you end up using a huge IF statement, it may be helpful to write out all the combinations in a table, and what the results should be for each, to help us understand what exactly this is meant to do.

 

[Gerald Higgins]

Re: help with nested if statement

I started mapping out your combinations, couldn't be bothered to do it for all of them but found a pattern after the first half dozen or so.

This appears to produce the correct results . . .

=COUNT(C19:C32)-2+(C32=1)*30

This assumes that cells such as C19 etc are blank if they do not contain 1 or 2.

 

[rayrickson]

Re: help with nested if statement

thank you all for your responses and time!

so what i am working with is a 500,000 numbers between 1 and 3, and i want to count how many in a row there are of each number and keep a running next to the column. like if there were 12 1's in a row im trying to get the column next to it to say "12" and then if 3 2's, the 3, etc and down through the whole column... how do i attach a picture here?


thanks again

 

[sheetspread]

Re: help with nested if statement

You can post a link to a hosting site or paste from a table formatting program like MrExcelHtml maker

 

[Gerald Higgins]

Re: help with nested if statement

See post #7