Question about deleting rows and formulas

[richard.anderson970]

Hi guys!

After beating my head into the wall for the past hour, I though I'd
come and ask the experts about the best way to solve my problem. I
have a simple worksheet that looks like so:

15 15
15 30
15 45
15 60
15 75

The first column contains '15' and the second column does the formula
=b1+a2, =b2+a3, and so on. The only row that isn't like this is the
first row in which the formula is just =a1.

My question is, how do I make it so that if I delete, say, the 3rd row,
that my formulas will adjust automatically? I've attempted to play
around with absolute references, but I still had problems when I
deleted rows. Granted this is an extremely simple example, but
hopefully, I'll be able to extend the solution to my very complex
spreadsheet.

Thanks,

Rich

 

[RagDyeR]

Try this in B1:

=SUM($A$1:A1)

And copy down as needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi guys!

After beating my head into the wall for the past hour, I though I'd
come and ask the experts about the best way to solve my problem. I
have a simple worksheet that looks like so:

15 15
15 30
15 45
15 60
15 75

The first column contains '15' and the second column does the formula
=b1+a2, =b2+a3, and so on. The only row that isn't like this is the
first row in which the formula is just =a1.

My question is, how do I make it so that if I delete, say, the 3rd row,
that my formulas will adjust automatically? I've attempted to play
around with absolute references, but I still had problems when I
deleted rows. Granted this is an extremely simple example, but
hopefully, I'll be able to extend the solution to my very complex
spreadsheet.

Thanks,

Rich

 

[richard.anderson970]

So would the formula in b2 be: =SUM($B$1:B1)??

The formula you gave me takes the value in A1 and adds to....A1?

 

[Bob Phillips]

No , exactly as RD said.

The formula adds all cells starting at A1 until A of the current row.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[RagDyeR]

Please just *TRY* as suggested.
THEN, make comments!

With numbers in Column A, from A1 to A100,

Enter formula in B1:

=SUM($A$1:A1)

And drag down to copy.

NOW ... post back with a comments.

 

[flummi]

The formula, as you copy it down, does this:

=sum($A$1:A1) which takes the value in A1
=sum($A$1:A2) which sums the values in A1 and A2
=sum($A$1:A3) which sums the values in A1 to A3

etc.

If you delete a row the formulas will be adjusted.

This is what you had anyway.

Hans

 

[edcosoft]

No. He gave you the formula =SUM($A1:A1) in cell B1. When you copy
it to B2 it becomes SUM($A$1:A2). Any cell in column B will add all the
items in column A, regardless of what Rows you delete or add. ed

 

[richard.anderson970]

Ok, I think my example may have been a bit too simplistic. Let's try
one a little more complex.

Row 1: 18 18
Row 2: 14 32
Row 3: 10 42
Row 4: 3 45
Row 5: 20 65
Row 6: 5 70

So formula in B1 is =SUM($A$1:A1). Formula in B2 is.....
=SUM($B$1:B1)?

 

[richard.anderson970]

There, that clears things up! Thank you!

-Rich

 

[richard.anderson970]

I did try. According to your directions, I got '15' all the way down.

 

[Bob Phillips]

No, B1 is =SUM($A$1:A1) and just copy down.

The $A$1 anchors it to the first cell, but as you copy down the second A1 is
incremented, so it sums to the current row.

Try it and see.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[flummi]

Funny debate this. Why don't you just try an example:

1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45

with the formula RD suggested in B1 and down.

1 1
2 3
3 6
4 10
6 16
7 23
8 31
9 40

After deletion of row 5

The request was to provide a running total. That is exactly what is
shown above.

Hans

 

[richard.anderson970]

I found Bob's explanation to be more helpful. It wasn't clear that it
should be sum($a$1:a1), sum($a$1:a2), etc. When someone says just take
this formula and copy it into all your cells without any explanation of
what it's doing, I hesitate. Plus, taking that formula and pasting it
exactly as specified in cells, would be incorrect. All I was asking
for was for some clarification on what the formula was doing, not a,
"here just use this" with no explanation.

 

[edcosoft]

Sorry richard, but one of the first things you learn in Excel is about
copying absolute and regular formulas and what happens to them when
copied. It's difficult to know in advance that you woudn't know what
would happen to =SUM($A$!:A1) in cell B-1 when you copied it "down"
into cell B-2, B-3, etc. Someone even suggested you try it and see what
happens. ed

 

[richard.anderson970]

Is one of the first things you learn also not trying to figure out what
something is doing or what a formula means? This just paste it in and
trust me crap is for the birds. And I'm not a newbie.... been using
Excel for 15+ years.