+ Reply to Thread
Results 1 to 5 of 5

Insert a column while not changing formula

  1. #1
    Registered User
    Join Date
    02-02-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Insert a column while not changing formula

    I have read through countless forums and tried several times to get this fixed by reading others problems/solutions. This may also be something pretty basic to most of you, but to me its been very frustrating.

    I keep track of all of my grocery costs in an spreadsheet. It has the product in column "A", the size in column "B", average cost in column "C", and the cost per unit in column "D". I insert a new date and the prices in column "E" and shift older dates to the right. I need a way to shift the column to the right and have the average price reflect all of the dates, instead of shifting the column numbers. Aditionally I need to be able to copy this formula down the rows in the event of adding a new product.

    I have experimented with absolutes ($E$2), and tried figuring out INDIRECT and INDEX functions, all of which has failed me.

    Any ideas? I have attached the file for assistance....THANK YOU!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,681

    Re: Insert a column while not changing formula

    Hi

    If you add a column within a range rather than at the edge of it, the formula automatically adapts to include the new column. So I’d suggest your easiest solution is to insert a hidden blank column to the left of the one with your latest data. Then, whenever you add another column, your formula's range will expand automatically to include the new column.

    I have done this with your attached example sheet. Column E is now blank and hidden, if you insert a column to the left of column F your formula still works.
    Attached Files Attached Files
    Last edited by NickyC; 02-02-2011 at 09:43 PM. Reason: typo

  3. #3
    Registered User
    Join Date
    02-02-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Insert a column while not changing formula

    Isn't it amazing how the simple things go unused? Thank you. I way over complicated that. I know there has to be a formula out there that could work, but I just needed this to function properly.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,250

    Re: Insert a column while not changing formula

    Hi kevironi and welcome to the forum.

    Try this. In C2 put this formula and copy it down.
    Please Login or Register  to view this content.
    See if this allows you to insert columns and still give the average you want.

    On the other hand. I think you need to start learning pivot tables. Put all your dates in the same column and simply add to the data (at the bottom). I'd also separate out the Size into two columns. The first would be dimension (oz, cup, box, can, etc), and the second would be how many as a number (1, 5, 2.3, etc)
    This way you could buy Potatos at Lbs and 5.2. This would allow the average price much better. If you repeated your purchases, but used a validation lookup it might be pretty easy to fill in. If all the dates were in column E, a pivot table would be easy and give you averages also allowing sorting and filtering.

    hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    02-02-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Insert a column while not changing formula

    I will give it a shot. I sound like a genius to friends and family when it comes to MS Office, but I know there is always so much more that can be done and I only know a small (basic) portion. I will explore the pivot tables, thank you for your help!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1