Minimum 3 Cell Sum

[lholmes]

I have 2 columns of data, I would like to be able to tell when a minimum of three consecutive cells in col a sum to less 21s, then report that sum in the same row in col c.
Any ideas anyone??
Sample data below

RECOVERY SPRINTS
42 1
61 6
18 1
25 7
3 1
5 2
9 1
31 2
5 6
24 1
1 3
26 4
5 1
2 1
17 2
63 3

 

[Jonmo1]

Assuming row 1 is headers, data starts in row 2

in C 4 put formula

=IF(SUM(A2:A4)<21,"Less than 21","")
Fill down

Hope this helps...

Personal.xls
	A	B	C	D
1	RECOVERY	SPRINTS	Formula
2	42	1
3	61	6
4	18	1
5	25	7
6	3	1
7	5	2
8	9	1	Lessthan21
9	31	2
10	5	6
11	24	1
12	1	3
13	26	4
14	5	1
15	2	1
16	17	2
17	63	3
Sheet1

 

[Jonmo1]

Oh wait, you wanted to show that sum if it was less than 21, I just put in a text flag...

chagne the formula to

=IF(SUM(A2:A4)<21,Sum(A2:A4),"")

 

[lholmes]

You fell into the same trap as I did, I have this already, but wanted to be able to min of 3 cells, so might be more. Couldn't figure that one out.

 

[facethegod]

You fell into the same trap as I did, I have this already, but wanted to be able to min of 3 cells, so might be more. Couldn't figure that one out.

If your data is in A1:A16 try

=IF(A1>20,"",IF(LOOKUP(1,1/(SUBTOTAL(9,OFFSET(A1,,,ROW(A1:$A$16)-ROW(A1)+1,1))<21),ROW(A1:$A$16)-ROW(A1)+1)>=3,SUM(A1:INDEX(A1:$A$16,LOOKUP(1,1/(SUBTOTAL(9,OFFSET(A1,,,ROW(A1:$A$16)-ROW(A1)+1,1))<21),ROW(A1:$A$16)-ROW(A1)+1))),""))

 

[Domenic]

Assuming that the data does not contain negative numbers, maybe...

C1, copied down:

=IF(SUM(A1:A3)>=21,"",LOOKUP(21,SUBTOTAL(9,OFFSET(A1:$A$16,,,ROW(A1:$A$16)-ROW(A1)+3))))

Hope this helps!

 

[Domenic]

Assuming that the data does not contain negative numbers, maybe...

C1, copied down:

=IF(SUM(A1:A3)>=21,"",LOOKUP(21,SUBTOTAL(9,OFFSET(A1:$A$16,,,ROW(A1:$A$16)-ROW(A1)+3))))

Hope this helps!

Actually, a couple of changes need to be made...

1) Change

+3

to

+1

2) If the actual data does not contain decimal numbers, as per sample data, change...

21

to

20

Otherwise, change...

21

to

20.9999999999999

Hope this helps!

Hope this helps!

 

[lholmes]

Thanks for the help guys