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

 

[sheetspread]

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)?

 

[sheetspread]

Re: help with nested if statement

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

Sounds like you want this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1								1	2	3
2	3	1	2	1	2	2	3	2	3	2
3	2	1	1	1	1	1	1	2	3	2
4	1	3	2	2	3	1	3	2	3	2
5	2	3	3	3	3	2	1	2	3	2
6	1	1	2	2	1	2	3	2	3	2
7	3	3	2	2	1	2	1	2	3	2
8	1	3	2	3	2	3	2	2	3	2
9	1	1	1	3	3	2	1	2	3	2
10	3	3	3	1	1	1	1	2	3	2
11	1	3	1	3	2	2	1	2	3	2
12	1	2	1	1	3	3	2	2	3	2
13	2	2	2	3	2	3	2	2	3	2
14	2	2	2	1	3	2	1	2	3	2
15	2	2	2	3	3	3	1	2	3	2
16	1	1	1	1	1	1	2	2	3	2
17	3	1	2	1	2	2	2	2	3	2
18	2	1	2	1	1	2	1	2	3	2
19	1	1	1	1	1	2	3	2	3	2
Sheet6

Cell Formulas
Range	Formula
H2	=COUNTIF($A$2:$G$2,1)
I2	=COUNTIF($A$2:$G$2,2)
J2	=COUNTIF($A$2:$G$2,3)

 

[rayrickson]

Re: help with nested if statement

See post #7

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

 

[rayrickson]

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

 

[Gerald Higgins]

Re: help with nested if statement

Sorry but I'm not following that link.

Have you tried my suggestion from post #7 ?

 

[rayrickson]

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....

 

[Eric W]

Re: help with nested if statement

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

	C	D
1	Values	Streak
2	1	1
3	2
4	2
5	2
6	2
7	2	5
8	1	1
9	2	1
10	3
11	3
12	3	3
13	2
14	2
15	2	3
16	1	1
17	2
18	2	2
19	1	1
20	3	1

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D2 =IF(C2=C3,"",ROW()-SUM($D$1:$D1)-1) <tbody> </tbody>

<tbody>
</tbody>

 

[rayrickson]

Re: help with nested if statement

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

	C	D
1	Values	Streak
2	1	1
3	2
4	2
5	2
6	2
7	2	5
8	1	1
9	2	1
10	3
11	3
12	3	3
13	2
14	2
15	2	3
16	1	1
17	2
18	2	2
19	1	1
20	3	1

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D2 =IF(C2=C3,"",ROW()-SUM($D$1:$D1)-1) <tbody> </tbody>

<tbody>
</tbody>

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

now ill try it haha

 

[rayrickson]

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!!!