Keeping the same formula reference when inserting new cells

[skiman123321]

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?

 

[Joe Was]

$A$1, $B$1, and $C$1

$A$1:$C$1

 

[skiman123321]

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]

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!