|
Repeating an Excel Formula
I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
Repeating an Excel Formula
Maybe something like this:
With Numeric values in the columns B:K, beginning in Row_2 and some rows skipped A2: =SUM(B2:K2) A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0)) The formula in A3 can be copied anywhere down Col_A, skipping rows if needed. It will continue to calculate the cumulative total. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: I have an Excel spreadsheet with numbers that I want to sum in a row, carry a cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
Repeating an Excel Formula
Ron,
I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... -- Bae "Ron Coderre" wrote: Maybe something like this: With Numeric values in the columns B:K, beginning in Row_2 and some rows skipped A2: =SUM(B2:K2) A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0)) The formula in A3 can be copied anywhere down Col_A, skipping rows if needed. It will continue to calculate the cumulative total. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: I have an Excel spreadsheet with numbers that I want to sum in a row, carry a cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
Repeating an Excel Formula
Let's work on the data structure first, to make sure we're talking about the
same thing. Here's my interpretation of what you want: With Refs Col_A Col_B Col_C Col_D Row_1 (blank) (blank) (blank) (blank) Row_2 6 1 2 3 Row_3 36 10 10 10 Row_4 54 5 6 7 Row_5 (blank) (blank) (blank) (blank) Row_6 57 1 1 1 Row_7 (blank) (blank) (blank) (blank) Row_8 75 5 6 7 Where Col_A contains the summary formulas. A2 simply sums from B2:D2 (1+2+3=6) A3 sums B3:D3 and adds it to the cumulative total of from above (10+10+10=30 + 6 from cell A2 =36) A4 sums B4:D4 and adds it to the cumulative total of from above (5+6+7=18 + 36 from cell A3 =54) etc Am I on the right track here? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: Ron, I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... -- Bae "Ron Coderre" wrote: Maybe something like this: With Numeric values in the columns B:K, beginning in Row_2 and some rows skipped A2: =SUM(B2:K2) A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0)) The formula in A3 can be copied anywhere down Col_A, skipping rows if needed. It will continue to calculate the cumulative total. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: I have an Excel spreadsheet with numbers that I want to sum in a row, carry a cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
Repeating an Excel Formula
You are on the right track! Now what?
-- Bae "Ron Coderre" wrote: Let's work on the data structure first, to make sure we're talking about the same thing. Here's my interpretation of what you want: With Refs Col_A Col_B Col_C Col_D Row_1 (blank) (blank) (blank) (blank) Row_2 6 1 2 3 Row_3 36 10 10 10 Row_4 54 5 6 7 Row_5 (blank) (blank) (blank) (blank) Row_6 57 1 1 1 Row_7 (blank) (blank) (blank) (blank) Row_8 75 5 6 7 Where Col_A contains the summary formulas. A2 simply sums from B2:D2 (1+2+3=6) A3 sums B3:D3 and adds it to the cumulative total of from above (10+10+10=30 + 6 from cell A2 =36) A4 sums B4:D4 and adds it to the cumulative total of from above (5+6+7=18 + 36 from cell A3 =54) etc Am I on the right track here? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: Ron, I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... -- Bae "Ron Coderre" wrote: Maybe something like this: With Numeric values in the columns B:K, beginning in Row_2 and some rows skipped A2: =SUM(B2:K2) A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0)) The formula in A3 can be copied anywhere down Col_A, skipping rows if needed. It will continue to calculate the cumulative total. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: I have an Excel spreadsheet with numbers that I want to sum in a row, carry a cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
Repeating an Excel Formula
Before this gets unnecessarily complicated,
let's see if something as simple as this will work for you.... Using my posted table... A2: =SUM(B$2:D2) Copy that formula down as far as you need. How'd we do? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: You are on the right track! Now what? -- Bae "Ron Coderre" wrote: Let's work on the data structure first, to make sure we're talking about the same thing. Here's my interpretation of what you want: With Refs Col_A Col_B Col_C Col_D Row_1 (blank) (blank) (blank) (blank) Row_2 6 1 2 3 Row_3 36 10 10 10 Row_4 54 5 6 7 Row_5 (blank) (blank) (blank) (blank) Row_6 57 1 1 1 Row_7 (blank) (blank) (blank) (blank) Row_8 75 5 6 7 Where Col_A contains the summary formulas. A2 simply sums from B2:D2 (1+2+3=6) A3 sums B3:D3 and adds it to the cumulative total of from above (10+10+10=30 + 6 from cell A2 =36) A4 sums B4:D4 and adds it to the cumulative total of from above (5+6+7=18 + 36 from cell A3 =54) etc Am I on the right track here? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: Ron, I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... -- Bae "Ron Coderre" wrote: Maybe something like this: With Numeric values in the columns B:K, beginning in Row_2 and some rows skipped A2: =SUM(B2:K2) A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0)) The formula in A3 can be copied anywhere down Col_A, skipping rows if needed. It will continue to calculate the cumulative total. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: I have an Excel spreadsheet with numbers that I want to sum in a row, carry a cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
Repeating an Excel Formula
ToolsOptionsCalculation.
Is "Automatic" checkmarked as it should be? Gord Dibben MS Excel MVP On Wed, 1 Nov 2006 13:24:01 -0800, Bonitae wrote: Ron, I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... |
Repeating an Excel Formula
That does it, thanks!
-- Bae "Ron Coderre" wrote: Before this gets unnecessarily complicated, let's see if something as simple as this will work for you.... Using my posted table... A2: =SUM(B$2:D2) Copy that formula down as far as you need. How'd we do? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: You are on the right track! Now what? -- Bae "Ron Coderre" wrote: Let's work on the data structure first, to make sure we're talking about the same thing. Here's my interpretation of what you want: With Refs Col_A Col_B Col_C Col_D Row_1 (blank) (blank) (blank) (blank) Row_2 6 1 2 3 Row_3 36 10 10 10 Row_4 54 5 6 7 Row_5 (blank) (blank) (blank) (blank) Row_6 57 1 1 1 Row_7 (blank) (blank) (blank) (blank) Row_8 75 5 6 7 Where Col_A contains the summary formulas. A2 simply sums from B2:D2 (1+2+3=6) A3 sums B3:D3 and adds it to the cumulative total of from above (10+10+10=30 + 6 from cell A2 =36) A4 sums B4:D4 and adds it to the cumulative total of from above (5+6+7=18 + 36 from cell A3 =54) etc Am I on the right track here? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: Ron, I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... -- Bae "Ron Coderre" wrote: Maybe something like this: With Numeric values in the columns B:K, beginning in Row_2 and some rows skipped A2: =SUM(B2:K2) A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0)) The formula in A3 can be copied anywhere down Col_A, skipping rows if needed. It will continue to calculate the cumulative total. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: I have an Excel spreadsheet with numbers that I want to sum in a row, carry a cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
Repeating an Excel Formula
Yes. it is thank you...
-- Bae "Gord Dibben" wrote: ToolsOptionsCalculation. Is "Automatic" checkmarked as it should be? Gord Dibben MS Excel MVP On Wed, 1 Nov 2006 13:24:01 -0800, Bonitae wrote: Ron, I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... |
Repeating an Excel Formula
Be careful what you ask for, because your restrictions without
explanation of why you have to skip some rows can make processing take a lot longer as your data increases http://groups.google.com/group/micro...TK2MSF TNGP10 Take a look at http://www.mvps.org/dmcritchie/excel/insrtrow.htm if you have to skip some rows for a reason in some fixed formatting you could change the references arount the empty rows, but it would be far better to avoid the empty rows. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Bonitae" wrote in message ... That does it, thanks! -- Bae "Ron Coderre" wrote: Before this gets unnecessarily complicated, let's see if something as simple as this will work for you.... Using my posted table... A2: =SUM(B$2:D2) Copy that formula down as far as you need. How'd we do? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: You are on the right track! Now what? -- Bae "Ron Coderre" wrote: Let's work on the data structure first, to make sure we're talking about the same thing. Here's my interpretation of what you want: With Refs Col_A Col_B Col_C Col_D Row_1 (blank) (blank) (blank) (blank) Row_2 6 1 2 3 Row_3 36 10 10 10 Row_4 54 5 6 7 Row_5 (blank) (blank) (blank) (blank) Row_6 57 1 1 1 Row_7 (blank) (blank) (blank) (blank) Row_8 75 5 6 7 Where Col_A contains the summary formulas. A2 simply sums from B2:D2 (1+2+3=6) A3 sums B3:D3 and adds it to the cumulative total of from above (10+10+10=30 + 6 from cell A2 =36) A4 sums B4:D4 and adds it to the cumulative total of from above (5+6+7=18 + 36 from cell A3 =54) etc Am I on the right track here? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: Ron, I am not sure if I am just not getting it or what, but that did not work. If I copy the formula from A3, it is the calculation from the previous formula. I am somwhat of a novice working with formulas, so maybe I am just not getting it... -- Bae "Ron Coderre" wrote: Maybe something like this: With Numeric values in the columns B:K, beginning in Row_2 and some rows skipped A2: =SUM(B2:K2) A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0)) The formula in A3 can be copied anywhere down Col_A, skipping rows if needed. It will continue to calculate the cumulative total. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Bonitae" wrote: I have an Excel spreadsheet with numbers that I want to sum in a row, carry a cummulative sum in a column, and be able to skip a few rows and still carry the cummulative number by picking up the same formula. However, once I skip a few rows with no information, Excel wants to only add the row and not carry forward the previous sum. Clear as mud?? Can you help? -- Bae |
| All times are GMT +1. The time now is 04:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com