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).
0
N (28)
5/3/2005 2:19:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
332 Views

Similar Articles

[PageSpeed] 57

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 N@discussions.microsoft.com> wrote in message 
news:1F8A4B29-2A00-4E70-9CD4-B4EBA45F545C@microsoft.com...
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). 


0
karlspam (74)
5/3/2005 5:32:25 AM
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 N@discussions.microsoft.com> wrote in message 
news:1F8A4B29-2A00-4E70-9CD4-B4EBA45F545C@microsoft.com...
|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). 


0
jrrech (1932)
5/3/2005 10:41:06 AM
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 N@discussions.microsoft.com> wrote in message 
> news:1F8A4B29-2A00-4E70-9CD4-B4EBA45F545C@microsoft.com...
> |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). 
> 
> 
> 
0
N (28)
5/3/2005 1:31:29 PM
Reply:

Similar Artilces:

How do I change margins from points to inches?
This system - as I read it - gives only the questions but not the answers. Would someone please answer the question in the above Subject line? For Word, you can set the desired unit of measurement in the (Word) Options dialog box. If you are using Word 2007, look at Office button | Word Options, Advanced category: "Show measurements in units of." In Word 2003, click Tools | Options, General tab. If the above doesn't help, chances are that you are seeing so-called character units. For assistance, see http://www.gmayor.com/Character_Units.htm. -- Stefan Blom Mi...

bindingSource.Position = ... doesn't change the current property.
Hello. I have bs as BindingSource When I do : bs.Position = 3. Current isn't changed to the position I gave. What should I do instead ? Thanks :) Am 29.05.2010 20:10, schrieb Mr. X.: > Hello. > I have > bs as BindingSource > > When I do : > bs.Position = 3. > > Current isn't changed to the position I gave. > > What should I do instead ? How do you see that it doesn't change? I've tried it based on the example here: http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.current(VS.90).aspx ...

Data Consolidation
Biff: Thanks so much for your input on the automation. After a small bit of tweaking, your formula worked like a charm. Now that I have the correlation multi-year table comparison automated, I would like to consolidate each commodities multi-year correlation into an overall correlation set. What I have now is five sets of correlation rows/commodity (representing the five historical data sets) which look like the following: Australian Dollar (AD) Correlations ============================== 1yr: C S PN SI SM 5yr: BO BP CD DX EU GC S SF SM 10yr: DX EU GC MP SF SI W 15yr: DX TU SP MP 20yr: CC G...

Append Data To Access Table
There is an Access table on the network. 15 users who do not have Access are connected to the network. Is there a way for each user to be able to enter one or more rows containing 3 or 4 columns to Excel on his machine and then press a button or something and append that data to the Access table on the network? Also would need to delete the data from the worksheet after the append. How would I prevent more than one user from appending data at the same time? Any suggestions on what the code would be? Thank you very much! Martin ...

Using VBA to change the color of Row and Column Headings
Is it possible to change the color of Excel 2003 column and/or row headings using VBA. I would like to have them appear, under certain conditions, as something other than the dull gray. I know how to inhibit their display but can't figure out how to change the color. Maybe it is not possible. Thank you. "Jack Gillis" <XXXXXXXX@widomaker.com> wrote... >Is it possible to change the color of Excel 2003 column and/or row >headings using VBA. I would like to have them appear, under certain >conditions, as something other than the dull gray. I know how to &g...

How to move Entourage X data to Entour. 2008 (in OS 10.6.3)
I have searched for a week an only find old scripts not applicable to Snow Leopard (Paul Berkowitz, Jolly roger, Brian Wainright). I can get none of these to work. MAybe I missed something. My initial data move with Entourage 2008 set up failed to move any Entourage X data into 2008. I was able to move my contacts into 2008 ( I forget now how I did that last week). For the last week plus I have been trying off and on to move my old mail into MSO Entourage 2008. With Brian Wainrights' script I was able to get my email into a folder on my Desk Top 'Mail Folders' (MBOX)...

How do i change the color for the printer
Please tell me asap!! Paint it? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "xxlilazndevilxxp" <xxlilazndevilxxp@discussions.microsoft.com> wrote in message news:B5DD8EDF-A227-43D8-A722-2663D9FEAAA3@microsoft.com... > Please tell me asap!! > ...

Keep an online copy of you Outlook data that you can synchronize across several locations and access it via web broswers
Hello, If you are an MS Outlook user, then you can try for 30 days completely free! please read the details below! Whether you are looking to backup your Microsoft Outlook data like contacts, calendar, tasks, notes, and To-Do List, or you are looking to backup your mobile phone, PDA, or PocketPC address book, InSynch Outlook Synchronization Service, is the service that can help any user who has multiple locations or within a workgroup to enhance the team work, by having a central backup on our server of his Microsoft Outlook 2000, 2002, XP or Microsoft Outlook 2003, calendar, address book, j...

Outlook plugin causes data file not to close properly
On a fairly regular basis we get a message when opening Outlook that the data did not close properly and needs checking. This then grinds the PC to a halt whilst it checks the data file but it never reports anything. This has only started happening since the CRM 4.0 Outlook client was installed. I have run rollup 1 but this has not solved the problem. Does anyone recognise this issue? I have the same problem. My Outlook is just now checking the data file, and I should be reading the emails already... It's not so bad for me as my data file is not that big (less than 1 G), but we hav...

need formula to search column for a word and return another word
I have a multi-worksheet workbook where the 1st worksheet is an assets summary page and the other worksheets are for tracking individual assets. Each row of the individual asset worksheet represent an asset request. A column on the assets worksheet titled "order status" will have one of three inputs for each request: "Open", "Pending", or "Complete". On the summary worksheet, each row represents an individual asset. There is a column on the summary worksheet titled "Item Status". I need a formula to look at the asset worksheet "...

Cannot move/copy Excel 97 worksheet
Does anyone know why, when I right-click on a worksheet in an Excel 97 workbook, the More or Copy command on the pop-up window is grayed out? The only commands available are Select All Sheets and View Code. The workbook on which this file was based enables the user to move/copy worksheets, but this one doesn't. Both are password protected. What type of things could the user have done to this file that would make this command be disabled? In other words, what should I look for to resolve the problem? Thanks! Catherine Hi does it work then you remove the password restrictions? ...

How to remove duplicate rows containing same data
I've got a sheet with 1410 row, but there may be only 100-150 unique entries in column A (columns B-C have varying data, thus the repeating of the same data in column A). I want to distill the sheet down so that there is only the same number of rows that there is unique entries in column A. Is there a formula or conditional formatting that will do this? Example: A123 Apple Red A123 Apple Green A123 Apple Yellow I don't want 3 rows with A123 in column A - I only want 1 row. I don't care about saving the data in the other columns. Thanks. Mark Never...

XML Serialization without Attributes?
Hello, I'm programming with Visual Studio 2005 for .net 2.0 in C#. Very often I use XML Serilization [1]. Some of the output files can be edited by the user with an external editor. What I don't like are some attributes which are inserted into the fily by the serializer, and which may confuse the user. Example: | <?xml version="1.0"?> | <Summary xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd = "http://www.w3.org/2001/XMLSchema"> | <CurrentCQI> | <Incident> | <CQI> <sn...

How To Log On Automatically Without Supplying Password With XP
My Vista Laptop logs on automatically to Windows Live Mail without having to type the password every time I reboot. However my desktop which runs XP SP3 requires me to type in the password every time I reboot or start up. Is there anyway I can get it to log on automatically? I have already gone to Tools/Accounts/Server and ticked remember password but the problem persists. Your advice would be appreciated. -- Kind Regards Chris There are two sign ins. The one you looked at is for synching mail. Each mail account has its own settings. There is also an optional s...

How do I change the default "file origin" when opening a tab delimited text file?
Excel2002 /Win2K Hi When I save a spreadsheet to tab delimited text and re-open it (and this is something I do a *lot* to get rid of micro$oft formatting and stray HTML coding...) it always defaults the "file origin" to be "932:Japanese (Shift-JIS)" instead of "1252:Western European (Windows)"! How do I get my MS Excel to default to the correct thing?! ie I want it to default to: "1252:Western European (Windows)" and not "932:Japanese (Shift-JIS)" whenever I open a .txt file ! Ship Shiperton Henethe ...

Template Wizard with Data Tracking
I would like to use the template wizard with data tracking. If I create a form using the forms toolbar to create list boxes, option boxes etc. can I have this data added to the linked database? Since the form controls are not cells, is this possible? Would this involve a lot of code? You can link the list boxes, etc. to worksheet cells (right-click the listbox, choose Format Control, enter a cell reference in the Cell Link box.) Dee wrote: > I would like to use the template wizard with data tracking. If I create a > form using the forms toolbar to create list boxes, option boxe...

Completed Task in error, need to change Activity Status
Hi; How can we change the Activity Status from Completed to open, as users can close in error, and we do not see a way of opening this task/note/etc. Thanks in advance for the reply ...

how do i change from "read only" mode ??
i created a flyer. when i try to save it to a cd, it won't let me. the promt says that the folder is "read only" and it won't let me save it. You should always save the file to your hard drive and then copy it to the CD. "kito" <kito@discussions.microsoft.com> wrote in message news:B1C804A5-A628-4759-B024-6C4243988083@microsoft.com... >i created a flyer. when i try to save it to a cd, it won't let me. the > promt says that the folder is "read only" and it won't let me save it. kito wrote: > i created a flyer. when i try ...

conversion formula for TV Frame rate's
Hi, I no there's probably a really easy way to do this, as I can work i out in my head without any trouble, but I'm trying to work out formula in Excel that converts lets say 100 frame's into 00:04:0 format. Can it be done? and am I really stupid ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Qboulder, I assume you want a formula for 24 frames per second. Try the following formula: =3DTEXT(INT(B18/1440),"00")&":"&TEXT(INT((B18-I...

Moving Money from XP to Vista
I've seen some posts about this but I'm not sure I'm clear on what to do. I recently added a Vista machine, Re-installed Money 2007 Deluxe, and copied my exist file from XP to the new location (C:\Program Files (x86)\Microsoft Money 2007\). None of my changes to the file seem to take. I tried moving the file to C:\Program mFiles\Microsoft Money 2007\ and had no better luck. I fianlly moved it to Documents and now it works. I don't like leaving my financial information in such a easy-to-view location, even though it's password protected. What are my options? Tha...

Financial Institution Name Change
Georgia Telco Credit Union changed their name effective today to Georgia's Own Credit Union. There are minor changes in the appearance of the webpage but the fields for logon id and password are the same (or at least they appear to be the same), however, when an online update is attempted it will no update. Gets error code -3003 saying that the provider web site has changed (duh). This financial institution uses the Yodlee interface to Microsoft for the online updates. Here's the question(s): Who needs to fix this problem? Microsoft or Yodlee or both? Anyone else out the...

SBS and moving to new hardware
He everyone, I have had a very strange experience with moving SBS 2003 sever to new hardware, I have managed to move about 20 of them with no problem at all ( all going to the same Intel server board S5500HCV board ). But on 2 occasions I have come across a problem with using PAE, now these 2 machine came from same model of system board ( another Intel board ) but when the system boots using PAE it will blue screen of death ( 0x050 Page fault in non paged area ) I have spoken to Intel about this and they cannot really offer any assistance other than changing the board or ...

Form comes out blank with no fields to enter data
When I try to create a form using the form wizard with the fields below ExpenseCategory ExpenseItem ExpenseDate Projected Cost Actual Cost I choose the tables and fields I want on my form, columnar layout, and standard style but when I finish the form comes out blank with no fields to enter data. Do you know why this might be happening? These are my tables TblExpenseCategory ExpenseCategoryID ExpenseCategory TblExpenseItem ExpenseItemID ExpenseCategoryID ExpenseItem TblExpense ExpenseID ExpenseDate Comments TblExpenseDetail ExpenseDetailID E...

Getting nodes with or without namespaces.
Hello, I'm having trouble trying to write a generic solution for parsing xml files. The xml-files I recieve can have namespaces or not, depending on the source - however, the treestructure is the same for all. An extra difficulty is that it is not certain that if a xml-file has namespaces, all nodes will use the namespaceprefix: some will and some will not. Adding namespaces to the namespacemanager is no problem. How do I know if a certain node uses a namespace prefix? Is there a property I can check? thanks, ru ru wrote: > How do I know if a certain node uses a namespace p...

Button to Change Form View?
Hi All, Is there anyway that I place a button on a main for form to switch the subform form view between data sheet and form view? Or is there another way? I don't mind manual workaround like right clicking something, but can't spot it. If the response is that I can do it in access basic then could somebody post the code please. Basic macro's are my limit I'm afraid. Ta. Bill. ...