Sum of row until you reach a certain amount
 

I want to add columns A2 through P2 until it equals the total in cell A1
Once the total would be met it would zero out the remainder of the cells.

For Example:

Cell A1 = 30
Cell A2 = 14
Cell B2 = 15
Cell C2 = 16
Cell D2 = 17 etc. (All the way to P2 which equals 29)

In the end I need:
Cell A3 to equal 14
Cell B3 to equal 29, (A2+B2) But we still need to add as we have not reached
30.
Cell C3 to equal 30, (A2+B2+C2) Because we only need 1 more to total to 30
Cells D4 to P4 to equal 0 as we have equaled or matched Cell A1 which is 30

I hope you can help!

Andman

Hi Andman

assuming D4:P4 to equal 0 was a typo and you meant D3:P3
the formula you need are
in A3
=A2
in B3
=IF(SUM($A$2:A2)=$A$1,0,MIN(B2+A3,$A$1))
and fill across to P3

Cheers
JulieD

"Andman" wrote in message
...
I want to add columns A2 through P2 until it equals the total in cell A1
Once the total would be met it would zero out the remainder of the cells.

For Example:

Cell A1 = 30
Cell A2 = 14
Cell B2 = 15
Cell C2 = 16
Cell D2 = 17 etc. (All the way to P2 which equals 29)

In the end I need:
Cell A3 to equal 14
Cell B3 to equal 29, (A2+B2) But we still need to add as we have not
reached
30.
Cell C3 to equal 30, (A2+B2+C2) Because we only need 1 more to total to 30
Cells D4 to P4 to equal 0 as we have equaled or matched Cell A1 which is
30

I hope you can help!

Andman

You deserve a thousand gold stars!

Thank you very much!

A

"JulieD" wrote:

Hi Andman

assuming D4:P4 to equal 0 was a typo and you meant D3:P3
the formula you need are
in A3
=A2
in B3
=IF(SUM($A$2:A2)=$A$1,0,MIN(B2+A3,$A$1))
and fill across to P3

Cheers
JulieD

"Andman" wrote in message
...
I want to add columns A2 through P2 until it equals the total in cell A1
Once the total would be met it would zero out the remainder of the cells.

For Example:

Cell A1 = 30
Cell A2 = 14
Cell B2 = 15
Cell C2 = 16
Cell D2 = 17 etc. (All the way to P2 which equals 29)

In the end I need:
Cell A3 to equal 14
Cell B3 to equal 29, (A2+B2) But we still need to add as we have not
reached
30.
Cell C3 to equal 30, (A2+B2+C2) Because we only need 1 more to total to 30
Cells D4 to P4 to equal 0 as we have equaled or matched Cell A1 which is
30

I hope you can help!

Andman

you're welcome <vbg thanks for the feedback

"Andman" wrote in message
...
You deserve a thousand gold stars!

Thank you very much!

A

"JulieD" wrote:

Hi Andman

assuming D4:P4 to equal 0 was a typo and you meant D3:P3
the formula you need are
in A3
=A2
in B3
=IF(SUM($A$2:A2)=$A$1,0,MIN(B2+A3,$A$1))
and fill across to P3

Cheers
JulieD

"Andman" wrote in message
...
I want to add columns A2 through P2 until it equals the total in cell A1
Once the total would be met it would zero out the remainder of the
cells.

For Example:

Cell A1 = 30
Cell A2 = 14
Cell B2 = 15
Cell C2 = 16
Cell D2 = 17 etc. (All the way to P2 which equals 29)

In the end I need:
Cell A3 to equal 14
Cell B3 to equal 29, (A2+B2) But we still need to add as we have not
reached
30.
Cell C3 to equal 30, (A2+B2+C2) Because we only need 1 more to total to
30
Cells D4 to P4 to equal 0 as we have equaled or matched Cell A1 which
is
30

I hope you can help!

Andman