Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to add 6% sales tax to the amount in a cell/column | Excel Discussion (Misc queries) | |||
Pivot table hidding zeros in sum of amount | Excel Discussion (Misc queries) | |||
How do I enter a negative dollar amount? | Excel Discussion (Misc queries) | |||
To find a combination of numbers that equal a set amount? | Excel Discussion (Misc queries) | |||
Loan Amortization Template - Amount of Final Payment | Excel Discussion (Misc queries) |