Option 1:
Lets say the name of your range is my_data like this one:
This is the formula to use:
Public Function calculate_avg(rng As Range) As Double
calculate_avg = WorksheetFunction.Average(Range(rng.Cells(1, 1).Address, Cells(rng.Rows.Count + rng.Cells(1, 1).Row - 1, rng.Columns.Count + rng.Cells(1, 1).Column - 1).Address))
End Function
Option 2:
Your named range is the following:
You want the average of the 2. and the 3. column. (C&D).
This is how you get it:
Option Explicit
Public Function calculate_avg(rng As Range, Optional l_starting_col As Long = 1, Optional l_end_col As Long = 1) As Double
Dim my_start As Range
Dim my_end As Range
Set my_start = Cells(rng.Cells(1, 1).Row, l_starting_col + rng.Cells(1, 1).Column - 1)
Set my_end = Cells(rng.Cells(rng.Rows.Count, l_end_col).Row, rng.Columns.Count - rng.Cells(1, l_end_col).Column + l_end_col)
'Debug.Print my_start.Address
'Debug.Print my_end.Address
calculate_avg = WorksheetFunction.Average(Range(my_start, my_end))
End Function
You pass as arguments the starting and the end column. Thus something like this:
?calculate_avg([my_test_big],2,3) in the immediate window returns 72,5. The same can be used as an Excel formula. Good luck! :)+
Option 3
Public Function calculate_avg_row(rng As Range, Optional l_row As Long = 1) As Double
Dim my_start As Range
Dim my_end As Range
Set my_start = Cells(rng.Cells(l_row, 1).Row, rng.Cells(l_row, 1).Column)
Set my_end = rng.Cells(l_row, rng.Columns.Count)
Debug.Print my_start.Address
Debug.Print my_end.Address
calculate_avg_row = WorksheetFunction.Average(Range(my_start, my_end))
End Function
This one works like this:
calculate_avg_row([test_rng],5)
And gives the average of the 5th row of the named range, including all columns of the named range.
