If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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(C1A1,A1,C1) then in D2 place: =IF(C2C1,0,IF(SUM($C$11)=$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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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(A2A1,A1,A2) B3: =IF(A3A2,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(C1A1,A1,C1) then in D2 place: =IF(C2C1,0,IF(SUM($C$11)=$A$1,D1,$A$1-SUM($C$1:C1))) copy this formula through P2 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I have power point viewer, but i need to make a power point produ. | PowerPoint75 | Powerpoint | 1 | March 10th, 2005 10:28 PM |
auto run power point files | badger man | Powerpoint | 3 | January 24th, 2005 03:11 AM |
Power Point 2000 slides do not show up in Power Point 2003 | mrt1 | Powerpoint | 1 | January 19th, 2005 11:47 PM |
power point isnt in small bisnuse xp 2002 | david | Powerpoint | 2 | June 22nd, 2004 02:02 PM |
Retaining Data Point Colors while Deleting a previously plotted Column? | Ken Black | Charts and Charting | 4 | October 17th, 2003 03:54 PM |