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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

L
  • Question Question
Help with formula to keep leading zero in cell - cant format as date
Replies
4
Views
72
6StringJazzer
6StringJazzer
V
  • Question Question
Help with my excel sheet
Replies
2
Views
294
victoriar
V
VinceF
  • Question Question
Circular Reference Error
Replies
8
Views
614
VinceF
VinceF
S
  • Solved
Convert to dynamic array
Replies
7
Views
184
sharshra
S
C
  • Question Question
Print from database
Replies
0
Views
280
Charlreena
C

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

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