Keeping the same formula reference when inserting new cells

skiman123321

New Member
Joined
Aug 3, 2005
Messages
13
I have a number in cell A1, B1, and C1. Cell D1 contain the formula to average A1, B1, and C1. I need to insert three new rows, so A1, B1, C1, and D1, now become D1, E1, F1, and G1 respectively. I want the average formula that was originally in cell D1, now G1, to again average the new values in A1, B1, and C1. How do keep the reference to A1, B1, and C1 so that the average formula always looks at those cells as new cells are inserted?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

skiman123321

New Member
Joined
Aug 3, 2005
Messages
13
I think I did what you suggested. The formula in D1 is =AVERAGE($A$1:$C$1). When I insert three new columns, D1, now G1 says =AVERAGE($D$1:$F$1). Obviously I am missing something.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Before you add any rows and before you select the formula cell, select the data range. Then from the Excel Toolbar: Insert - Name - Define...

Give your Data Range a Name, like: myAvg

Now in your Formula Cell put:

=Average(myAvg)

Now no matter how many rows you add, your Average data range will automaticall account for the added or deleted cells.


The only way to not account for the inserted rows, inserted above the data cells is to use macro code.

The $A$1 notation holds the cell when the formula is copied only. Row inserts are still dynamic and shift the data range in the formula in relation to the range change.

The Named Range works a little differently in that the Named Range Self - Adjusts to accomodate inserted rows as part of the whole data range.

The A1 notation is dynamic on copy - paste and on inserted rows/columns.

In macro code you can define a range and nomatter what you do that defined range is what you say it is. The problem is the cell that gets this set of cells Average value, in your case, must be sifted to accomodate the inserted Rows even though the actual Data Range will allways be the cell you indicate no matter what else happens on the sheet.

It would be best to write code tha does the whole operation [Insert rows, shift result cell address, post average and any other thing that happens] for you rather than use any sheet formulas!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,167
Members
410,667
Latest member
Gaexel
Top