Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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! ================================================== === wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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) wrote in message ups.com... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- wrote in message ups.com... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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)? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
There, that clears things up! Thank you!
-Rich |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
I did try. According to your directions, I got '15' all the way down.
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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) wrote in message oups.com... 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)? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about deleting rows and formulas
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to expand formulas to additional rows | Excel Discussion (Misc queries) | |||
Question regarding how to search a column and print a row(s) | Excel Discussion (Misc queries) | |||
Question regarding how to search a column and print a row(s) | Excel Worksheet Functions | |||
Copy/Insert rows with formulas | Excel Worksheet Functions | |||
Insert rows | Excel Worksheet Functions |