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. 


Thread Tools  Display Modes 
#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). 
Ads 
#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). > > > 
Thread Tools  
Display Modes  


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 