Sum of row untill you reach a certain point (2)

I have a twist on a problem that was answered earlier by the very intelligent
JulieD.

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 15, But we still need to add as we have not reached
30.
Cell C3 to equal 1, Because we only need 1 more to total to 30
Cells D3 to P3 to equal 0 as we have equaled or matched Cell A1 which is 30

I hope you can help again!

Andman

Hi, Andman. I'm not sure if JulieD is around, so I'll jump in. Without VBA,
you can't have a formula change the contents of another cell. You can,
however, use a helper row to accomplish your task. In C2, place this
formula:
=IF(C1>A1,A1,C1)
then in D2 place:
=IF(C2<>C1,0,IF(SUM($C$1:D1)<=$A$1,D1,$A$1-SUM($C$1:C1)))
copy this formula through P2

HTH

Bruce


"Andman" wrote:

> I have a twist on a problem that was answered earlier by the very intelligent
> JulieD.
>
> 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 15, But we still need to add as we have not reached
> 30.
> Cell C3 to equal 1, Because we only need 1 more to total to 30
> Cells D3 to P3 to equal 0 as we have equaled or matched Cell A1 which is 30
>
> I hope you can help again!
>
> Andman
>
>

Kludgy, but try this in A3 and fill across:

=IF($A$1<=$A$2,MAX(IF(COLUMN()=1,$A$1)),IF(SUM($A$2:A2)
<$A$1,A2,IF(SUM(OFFSET($A$3,,,,COLUMN()-1))=$A$1,0,$A$1-
SUM(OFFSET($A$2,,,,COLUMN()-1)))))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have a twist on a problem that was answered earlier by
the very intelligent
>JulieD.
>
>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 15, But we still need to add as we have
not reached
>30.
>Cell C3 to equal 1, Because we only need 1 more to total
to 30
>Cells D3 to P3 to equal 0 as we have equaled or matched
Cell A1 which is 30
>
>I hope you can help again!
>
>Andman
>
>
>.
>

Sorry Andman, I used the wrong starting range in my formulas. Row 3 should
be the helper row, and start placing the formulas in A3 and B3 as such:

A3: =IF(A2>A1,A1,A2)

B3: =IF(A3<>A2,0,IF(SUM($A$2:B2)<=$A$1,B2,$A$1-SUM($A$2:A2)))
and copy this through P3

HTH

Bruce

"swatsp0p" wrote:

> Hi, Andman. I'm not sure if JulieD is around, so I'll jump in. Without VBA,
> you can't have a formula change the contents of another cell. You can,
> however, use a helper row to accomplish your task. In C2, place this
> formula:
> =IF(C1>A1,A1,C1)
> then in D2 place:
> =IF(C2<>C1,0,IF(SUM($C$1:D1)<=$A$1,D1,$A$1-SUM($C$1:C1)))
> copy this formula through P2