# Keeping the same formula reference when inserting new cells

#### skiman123321

##### New Member
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?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Joe Was

##### MrExcel MVP
\$A\$1, \$B\$1, and \$C\$1

\$A\$1:\$C\$1

#### skiman123321

##### New Member
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
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!

Replies
0
Views
87
Replies
4
Views
226
Replies
4
Views
199
Replies
6
Views
267
Replies
8
Views
136

1,141,937
Messages
5,709,423
Members
421,636
Latest member
kelseyacheson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back