Long nested IF statements not going to subsequent steps

[snipescc]

I'm doing a large set of nested IF/AND statements that isn't working correctly. It's supposed to check if both a user ID is the same as one in the cells 10 rows away, and if the sum of 10 cells in a column is equal to 10. If it is, it subtracts some timestamps. If it isn't, it's supposed to check the same things, but for 11 rows, then 12, 13,14 ect. I've gotten the formula to work with just 2 steps (checking 10 and 11 rows) as a test, but the large 10 step one doesn't work right. If the first condition is met, it's fine, but if it isn't, sometimes (but not always) it jumps to the final "false" result. It's probably a syntax error, but I've never done an IF statement this long. Anyone spot the mistake?

Column A is a user ID, B is a timestamp, and K gives me 1 if it is the first instance of an address, and 0 if it isn't. The idea is to see how long it takes for someone to go to 10 addresses.


The one that works:
=IF(AND(A39=A30,SUM(K30:K39)=10),B39-B30,IF(AND(A39=A29,SUM(K29:K39)=10),B39-B29,"unmeasured"))

The one that doesn't:
=IF(AND(A25=A16,SUM(K16:K25)=10),B25-B16,IF(AND(A25=A15,SUM(K15:K25)=10),B25-B15,IF(AND(A25-A14,SUM(K14:K25)=10),B25-B14,IF(AND(A25=A13,SUM(K13:K25)=10),B25-B13,IF(AND(A25=A12,SUM(K12:K25)=10),B25-B12,IF(AND(A25=A11,SUM(K11:K25)=10),B25-B11,IF(AND(A25=A10,SUM(K10:K25)=10),B25-B10,IF(AND(A25=A9,SUM(K9:K25)=10),B25-B9,IF(AND(A25=A8,SUM(K8:K25)=10),B25-B8,IF(AND(A25=A7,SUM(K7:K25)=10),B25-B7,"unmeasured"))))))))))

The same formula with line breaks for easy reading:
IF(AND(A25=A16,SUM(K16:K25)=10),B25-B16,
IF(AND(A25=A15,SUM(K15:K25)=10),B25-B15,
IF(AND(A25-A14,SUM(K14:K25)=10),B25-B14,
IF(AND(A25=A13,SUM(K13:K25)=10),B25-B13,
IF(AND(A25=A12,SUM(K12:K25)=10),B25-B12,
IF(AND(A25=A11,SUM(K11:K25)=10),B25-B11,
IF(AND(A25=A10,SUM(K10:K25)=10),B25-B10,
IF(AND(A25=A9,SUM(K9:K25)=10),B25-B9,
IF(AND(A25=A8,SUM(K8:K25)=10),B25-B8,
IF(AND(A25=A7,SUM(K7:K25)=10),B25-B7,"unmeasured"))))))))))

 

[snipescc]

Ah! problem solved. One of the equal signs was a minus sign. 422 character formula, thrown off by a slip of the finger.

 

[Jerry Sullivan]

If you are interested in an alternative to the long nested IF statements, you could try the formulas shown below.

This is more scalable and allows you to find the elapsed time for trips to a variable number of addresses entered in B28.

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K
6	UserID	Timestamp									First Instance
7	ID_101	7:00									0
8	ID_101	7:30									0
9	ID_101	8:00									0
10	ID_101	8:30									1
11	ID_102	9:00									0
12	ID_102	9:30									1
13	ID_102	10:00									0
14	ID_102	10:30									0
15	ID_102	11:00									1
16	ID_102	11:30									1
17	ID_102	12:00									0
18	ID_102	12:30									1
19	ID_102	13:00									1
20	ID_102	13:30									1
21	ID_102	14:00									1
22	ID_102	14:30									1
23	ID_102	15:00									0
24	ID_102	15:30									1
25	ID_102	16:00									1
26
27
28	Address Count	10
29	Index of 1st Trip	6
30	Elapsed Time	6:30
Sheet1

Cell Formulas
Range	Formula
B30	=IFERROR(IF($A$25=INDEX($A$7:$A$16,$B$29),$B$25-INDEX($B$7:$B$16,$B$29),NA()),"unmeasured")
B29	{=IF(SUM($K$7:$K$25)<$B$28,NA(),MATCH(SUM($K$7:$K$25)-$B$28+1,SUBTOTAL(9,INDIRECT("$K$7:"&ADDRESS(ROW($K$7:$K$25),COLUMN($K$7)))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

If using Excel 2010 or later, & with Jerry's layout & idea of putting the number of addresses required in B28, you could also try this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
6	UserID	Timestamp									First Instance
7	ID_101	7:00									0
8	ID_101	7:30									0
9	ID_101	8:00									0
10	ID_101	8:30									1
11	ID_102	9:00									0
12	ID_102	9:30									1
13	ID_102	10:00									0
14	ID_102	10:30									0
15	ID_102	11:00									1
16	ID_102	11:30									1
17	ID_102	12:00									0
18	ID_102	12:30									1
19	ID_102	13:00									1
20	ID_102	13:30									1
21	ID_102	14:00									1
22	ID_102	14:30									1
23	ID_102	15:00									0
24	ID_102	15:30									1
25	ID_102	16:00									1
26
27
28		10
29
30		6:30
Elapsed Time

 

[Jerry Sullivan]

Ah... very nice Peter. I hadn't noticed the Aggregate function previously. Thanks for pointing that out.

 

[Peter_SSs]

Ah... very nice Peter. I hadn't noticed the Aggregate function previously. Thanks for pointing that out.

No problem. I'm gradually getting a bit used to Aggregate but find it frustrating that you can't feed array-like data into it for many of its function numbers.