A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I move data without changing an associated formula?



 
 
Thread Tools Display Modes
  #1  
Old May 3rd 05, 03:19 AM
Al N
external usenet poster
 
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).
Ads
  #2  
Old May 3rd 05, 06:32 AM
Karl Burrows
external usenet poster
 
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  
Old May 3rd 05, 11:41 AM
Jim Rech
external usenet poster
 
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  
Old May 3rd 05, 02:31 PM
Al N
external usenet poster
 
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).
>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 10:45 AM.


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