Finding a sum withing a column of numbers

[dzeunen]

I didn't know how to title this but this is what I am looking for.

If I have a list of numbers like below and I want to know when I reach 171, what formula would I use. I the perfect world it would let me know where the row is split. I just don't know any other way to explain it. Any help would be appreciated.
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12 Example being this is where I reach 171 but only 3 of this 12 of it is what I need.
12
12
12
12
12
240

 

[jtakw]

Hi,

I think this answers part of your question, I don't understand what else you're asking:

Book1
	A	B
1	12
2	12
3	12
4	12
5	12
6	12
7	12
8	12
9	12
10	12
11	12
12	12
13	12
14	12
15	12	Reached 171
16	12
17	12
18	12
19	12
20	12
21	240
Sheet311

Cell Formulas
Range	Formula
B1	=IF(AND(SUM(A$1:A1)>=171,SUM(A$1:A1)-A1<171),"Reached 171","")

Formula copied down.

 

[Peter_SSs]

Perhaps this is what you want? The value in () is how much of the current row is used to reach the target.

Excel Workbook
	A	B	C
1	Numbers	Reached?	Target
2	12		171
3	12
4	12
5	12
6	12
7	12
8	12
9	12
10	12
11	12
12	12
13	12
14	12
15	12
16	12	Reached 171 (3)
17	12
18	12
19	12
20	12
21	12
22	240
Reach Sum

 

[jtakw]

Ok, I understand the 2nd part of your question now (since Peter posted):

Book1
	A	B	C	D
1	12			171
2	12
3	12
4	12
5	12
6	12
7	12
8	12
9	12
10	12
11	12
12	12
13	12
14	12
15	12	Reached 171 (used 3 on this row)	Reached 171 (used 3 on this row)
16	12
17	12
18	12
19	12
20	12
21	240
Sheet311

Cell Formulas
Range	Formula
B1	=IF(AND(SUM(A$1:A1)>=171,SUM(A$1:A1)-A1<171),"Reached 171 (used "&171-(SUM(A$1:A1)-A1)&" on this row)","")
C1	=IF(AND(SUM(A$1:A1)>=D$1,SUM(A$1:A1)-A1),"Reached "&D$1&" (used "&D$1-(SUM(A$1:A1)-A1)&" on this row)","")

Use B1 formula where 171 is hard-coded in the formula.
Use C1 formula where 171 is a cell reference (D1), which you might change as needed.