Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Al N
 
Posts: n/a
Default 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   Report Post  
Karl Burrows
 
Posts: n/a
Default

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   Report Post  
Jim Rech
 
Posts: n/a
Default

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   Report Post  
Al N
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
can I move data from workbook A to B using a conditional formula Bonnie Excel Worksheet Functions 2 April 14th 05 09:28 PM
how do you prevent data from changing values when sorting linked . Cassie Excel Discussion (Misc queries) 0 March 4th 05 11:45 AM
how can I move cells after data input without using enter or tab mull Excel Discussion (Misc queries) 1 March 2nd 05 06:53 PM
How can deleted data reappear in a refreshed pivot table in Excel excel_user123456 Excel Discussion (Misc queries) 3 February 23rd 05 09:34 PM
How to move data on worksheet Bonnie Excel Discussion (Misc queries) 3 February 23rd 05 06:49 PM


All times are GMT +1. The time now is 06:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"