I have a spreadsheet where I want Column D to show the Average for columns E thru P for a row. I need to be able to add a new column (i.e. insert a new column between D and E, thereby making the existing E become F) without the the Average function in D changing - I don't want it to change and now show Average for F thru Q but to continue to show Average for E thru P. I am rather new to using VB in Excel but figure there must be some way to do this. Any help would be greatly appreciated.

As far as I know this should happen automatically. If the original formula was =AVERAGE(E2:P2) when you add the new column, the formula should change to =AVERAGE(F2:Q2). That is one of the "problems" with inserting a row or column at the end of a range - the fomula merely moves the endpoints and does not include the new row or column. Most of the time this isn't what you want, but in this case, it works for you.

Maybe I didn't make it clear but I want the range for Average to continue to be E-P after I add the new column. The formula by default does change to F-Q but that is not what I want to happen. As it currently is, after I add the new column I have to go through and adjust all the formula ranges for a couple dozen cells in column D.

you can try this code:

just put it on any cell, say in A1, =fMyAverage.

Arnel, Appreciate your assistance. The code you provided didn't do exactly what I wanted but it got me pointed in the right direction. Came up with the following which does what I need.

Function kAvg(strRange as String)
Dim objRange as Range
Set objRange = Range(strRange)
kAvg = WorksheetFunction.Average(objRange)
End Function


In a cell, I enter =kAvg("E11:P11") and the result is the average of the cell in the range. This allows me to specify any range and the range doesn't change if I add/delete rows/columns.
Again greatly appreciate the assist.

You got it , but you have left out one important thing.

Function kAvg(strRange as String)
Dim objRange as Range
Application.Volatile
Set objRange = Range(strRange)
kAvg = WorksheetFunction.Average(objRange)
End Function

Without this, your formula will not perform automatic recalculation. Try changing any data on E11 - P11 and your function will not recalculated without the said code.

Happy Averaging!