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?
 

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).

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!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top