Repeating an Excel Formula

B

Bonitae

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?
 
R

Ron Coderre

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
 
B

Bonitae

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...
 
R

Ron Coderre

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
 
B

Bonitae

You are on the right track! Now what?
--
Bae


Ron Coderre said:
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
Click to expand...
 
R

Ron Coderre

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
 
G

Gord Dibben

Tools>Options>Calculation.

Is "Automatic" checkmarked as it should be?


Gord Dibben MS Excel MVP
 
B

Bonitae

That does it, thanks!
--
Bae


Ron Coderre said:
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
Click to expand...
 
D

David McRitchie

Top