Insert a column while not changing formula

**kevironi** · 02-02-2011, 09:23 PM

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!

**NickyC** · 02-02-2011, 09:43 PM

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.

**kevironi** · 02-02-2011, 09:47 PM

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.

**MarvinP** · 02-02-2011, 09:52 PM

Hi kevironi and welcome to the forum.

Try this. In C2 put this formula and copy it down.

=AVERAGE(OFFSET(D2,0,1,1,20))

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.

**kevironi** · 02-02-2011, 10:05 PM

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!!