Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I move data without changing an associated formula?
I have created an Excel worksheet which I update with new values each day
(golf scores). I keep the last 10 scores but want an average of the last 5 scores. How can I move the data one column to the left and not change the averaging formula so that when I post the new score it still uses the original formula (sum of G5 to K5 devided by 5). |
#2
|
|||
|
|||
If you put a "$" before any cell reference, it will "freeze" the formula
value. So, if your formula references cell G5, you can use $G5 to freeze the column or G$5 to freeze the row or $G$5 to freeze the entire cell reference. "Al N" <Al wrote in message ... I have created an Excel worksheet which I update with new values each day (golf scores). I keep the last 10 scores but want an average of the last 5 scores. How can I move the data one column to the left and not change the averaging formula so that when I post the new score it still uses the original formula (sum of G5 to K5 devided by 5). |
#3
|
|||
|
|||
I would suggest that you not move (cut/paste) the data since the formulas
will follow it regardless if whether the formulas are absolute or relative. I'd do a copy/paste and then clear the last column K for the new entries. You can also use a formula that indirectly references the range to the left so that it does not adjust when you cut/paste. For instance this formula when placed in cell I8: =SUM(OFFSET(I8,0,-3,1,3)) -- Jim "Al N" <Al wrote in message ... |I have created an Excel worksheet which I update with new values each day | (golf scores). I keep the last 10 scores but want an average of the last 5 | scores. How can I move the data one column to the left and not change the | averaging formula so that when I post the new score it still uses the | original formula (sum of G5 to K5 devided by 5). |
#4
|
|||
|
|||
Thanks. The copy/paste does what I wanted. I should have thought of it. As
you said, I had already tried the absolute values, but they moved also moved with the cut/paste. Al N "Jim Rech" wrote: I would suggest that you not move (cut/paste) the data since the formulas will follow it regardless if whether the formulas are absolute or relative. I'd do a copy/paste and then clear the last column K for the new entries. You can also use a formula that indirectly references the range to the left so that it does not adjust when you cut/paste. For instance this formula when placed in cell I8: =SUM(OFFSET(I8,0,-3,1,3)) -- Jim "Al N" <Al wrote in message ... |I have created an Excel worksheet which I update with new values each day | (golf scores). I keep the last 10 scores but want an average of the last 5 | scores. How can I move the data one column to the left and not change the | averaging formula so that when I post the new score it still uses the | original formula (sum of G5 to K5 devided by 5). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I move data from workbook A to B using a conditional formula | Excel Worksheet Functions | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
how can I move cells after data input without using enter or tab | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
How to move data on worksheet | Excel Discussion (Misc queries) |