Sum of columns until you a certain amount
 

I have cell A1 which equals 12000.

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.

How do I do that?

Thanks for the help!
And

=MIN(A1,SUM(A2:P2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andman" wrote in message
...
I have cell A1 which equals 12000.

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.

How do I do that?

Thanks for the help!
And

Ok, I need to take this one step further.

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
Cell D4 to P4 to equal 0 as we have equaled or matched Cell A1 which is 30

I hope you can help!

Andman

"Bob Phillips" wrote:

=MIN(A1,SUM(A2:P2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andman" wrote in message
...
I have cell A1 which equals 12000.

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.

How do I do that?

Thanks for the help!
And

In A3 put =A2

In B3, put =IF(COLUMN()=1,B2,IF(OR(A3=$A$1,A3=0),0,MIN($A$1,S UM($A$2:B2))))

copy B3 across to P3

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andman" wrote in message
...
Ok, I need to take this one step further.

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
Cell D4 to P4 to equal 0 as we have equaled or matched Cell A1 which is 30

I hope you can help!

Andman

"Bob Phillips" wrote:

=MIN(A1,SUM(A2:P2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andman" wrote in message
...
I have cell A1 which equals 12000.

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.

How do I do that?

Thanks for the help!
And