I used Application.Caller which returns the cell as a Range object where the function is on the worksheet
The Quarters are always in row 2, and you want the latest 8 merged cells
The <>NB is always in row 3, and I used the same columns for those and the row-specific data to average
The .Caller row is the data to average
I added some comments to the macro
Capture.JPG
Option Explicit
Function Average8Quarters() As Variant
Dim iCallerRow As Long, iFirstColumn As Long, iLastColumn As Long, i As Long
Dim rData As Range, rCriteria As Range
'always update function even if the explicit inputs did not change
Application.Volatile
'if there's some error just return a standard Excel error and exit
On Error GoTo NiceExit
'get the row number one the worksheet where the function is called (e.g. B4 for the first, and B5 for the second
iCallerRow = Application.Caller.Row
'little more elegant
' .Caller returns the cell the function is in, and .Parent = the cell's parent, i.e. the worksheet
With Application.Caller.Parent
'get the first column in row 2 (i.e. AS2) of the merged cell column number (it's = 45)
iFirstColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column
'get the 'real' last column of the merged cell
'start with (2,45)
With .Cells(2, iFirstColumn)
'add 45 + 9 - 1 = 53 (2016 3rd Qtr has 9 columns
iLastColumn = .Column + .MergeArea.Columns.Count - 1
End With
'back up 7 more quarters and get that merged cell's starting column number
For i = 1 To 7
iFirstColumn = .Cells(2, iFirstColumn).End(xlToLeft).Column
Next I
'now we know that for the row the function is in, the data starts in
' (2, 3) and goes for 1 row and (53-3+1 = 51 more columns
Set rData = .Cells(iCallerRow, iFirstColumn).Resize(1, iLastColumn - iFirstColumn + 1)
'now we know that the criteria always is in row 3,and is the same number of columns as the data
' calculated above
Set rCriteria = .Cells(3, iFirstColumn).Resize(1, iLastColumn - iFirstColumn + 1)
End With
'call the worksheet function with the 2 ranges, and the fixed string critera
Average8Quarters = Application.WorksheetFunction.AverageIfs(rData, rCriteria, "<>NB")
'get out here so we don't execute the error handler code that follows
Exit Function
NiceExit:
Average8Quarters = CVErr(xlErrNum)
End Function