Nested if statement

R

rayrickson

New Member
Joined
Mar 21, 2018
Messages
9
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 :)
 
Sort by date Sort by votes
Re: help with nested if statement

A countif formula can run a cumulative count of each number, or do you want to label consecutive groups (or their totals)?
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Re: help with nested if statement

rayrickson said:
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
Click to expand...

Sounds like you want this:


Excel 2010
ABCDEFGHIJ
1123
23121223232
32111111232
41322313232
52333321232
61122123232
73322121232
81323232232
91113321232
103331111232
111313221232
121211332232
132223232232
142221321232
152223331232
161111112232
173121222232
182121121232
191111123232
Sheet6
Cell Formulas
RangeFormula
H2=COUNTIF($A$2:$G$2,1)
I2=COUNTIF($A$2:$G$2,2)
J2=COUNTIF($A$2:$G$2,3)
 
Last edited:
Upvote 0
Re: help with nested if statement

Gerald Higgins said:
See post #7
Click to expand...



hi Marcilio and Gerald. Here is a link to an excel sample of what im talking about. https://www.sendspace.com/file/p1ifqg


so, im trying to indicate lengths in a row of 1's or 2s. not 3's sorry as i said before :(

so in a column to the side of the numbers i am trying to make a running total streak lengths (at the end of four 2's in a row, "4," at the end of eleven 1's in a row, "11," and so on.

thanks again for your help :)
 
Upvote 0
Re: help with nested if statement

hi Sheetspread, not quite total number of 1's and 2's but a running total of streak lengths of 1's or 2's in a row, to the side in a column, which of course would include spaces, and could be further analyzed later. Thank you for your time :)
 
Upvote 0
Re: help with nested if statement

yes i did, Gerald, and it doesnt seem to do what my other formula did. But i do appreciate it, and perhaps also i didnt input it correctly. I just copied and pasted it in cell c1.... :)
 
Upvote 0
Re: help with nested if statement

I can't get to your file either, but something like this maybe?

CD
ValuesStreak

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(C2=C3,"",ROW()-SUM($D$1:$D1)-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: help with nested if statement

Eric W said:
I can't get to your file either, but something like this maybe?

CD
ValuesStreak

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=IF(C2=C3,"",ROW()-SUM($D$1:$D1)-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Eric, that is exactly what i mean!! thanks!

now ill try it haha :)
 
Upvote 0
Re: help with nested if statement

i think i got it now. Dear, Sheetspread, Gerald, Marcilio, and Eric, thanks again and have a nice day!!! :) :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Help with my excel sheet
Replies
2
Views
290
victoriar
V
VinceF
  • Question Question
Circular Reference Error
Replies
8
Views
607
VinceF
VinceF
S
  • Solved
Convert to dynamic array
Replies
7
Views
152
sharshra
S
C
  • Question Question
Print from database
Replies
0
Views
275
Charlreena
C
Z
  • Solved
Function/Formula to Show Weekdays Only
Replies
2
Views
534
zero269
Z

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top