Average of multiple columns

[cjop0]

I want to find a way of fixing this code so that the average can be calculated in the case where there is more than one column, but I have no idea how to do this. I'd really appreciate any solutions, thank you.



Function Aver(r As Range) As Double

Dim Sum As Double

If r.Columns.Count = 1 Then ' check that the range r has only one column
Sum = 0
For i = 1 To r.Rows.Count
Sum = Sum + r.Cells(i) ' we can use one index for Cells
' because range has only one column
Next i
Aver = Sum / r.Rows.Count ' function returns this value Aver if
' the range has only one column
Else
MsgBox "Data range has more than 1 column,”+vbNewLine+ “Average will be calculated only for the first column"
Sum = 0
For i = 1 To r.Rows.Count
Sum = Sum + r.Cells(i,1) '
Next i
Aver = Sum / r.Rows.Count' function returns this value Aver if
' the range has more than one column
End If

End Function

 

[Joe4]

Welcome to the Board!

Is there some reason why Excel's built-in AVERAGE function cannot be used?
It can handle multiple columns.

To use it in VBA, you can use WorksheetFunction.Average (see: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheetfunction-average-method-excel)

 

[steve the fish]

You just want to calculate the average of some cells? Why not just use the built in AVERAGE function or have i missed something?

 

[frank_AL]

cjop0,

Are you wanting to include all Columns that have data in them?
A B C D
2 4 6 8

Is it possible there would be any blank Columns with the data set?
A B C D
2 4 8

Is there a Header Row or does the data start on Row 1?


You could determine the last column and last row and then simply use the AVERAGE Function

For example if Column D is the last Column and Row 6 is the last Row in the code below
lc would be 4
lr would be 6

Dim lc As Long
Dim lr As Long
Dim aver As Long


lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, "A").End(xlUp).Row
aver = WorksheetFunction.average(Range(Cells(1, 1), Cells(lr, lc)))

This works even if there are blank Columns or blank Rows

 

[mikerickson]

The UDF in the OP takes the average of the cells in the first column of the range, unlike AVERAGE.

You could do something like

Function Aver(r As Range) As Double
    Aver = WorksheetFunction.Average(R.Columns(1))
End Function

or, instead of a formula like =Aver(someRange)

use native Excel =AVERAGE(INDEX(someRange,0,1))