Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print excel columns on less pages | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
Averaging columns | New Users to Excel | |||
combining 3 columns of same info for a pivot table | Excel Worksheet Functions |