macro for average of values -dynamic range of cells

prabhu.c · Aug 1st 2013

Hi,

I need to find average of the values , the count of the cells will be dynamic (may be 5 or even 200).

Thanks and Regards,
Prabhu.C

apo · Aug 4th 2013

Re: macro for average of values -dynamic range of cells

If you range of values are from A1 downwards.. try this..

Code

Sub Button1_Click()Dim Av As Double
Av = Evaluate("AVERAGE(A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row & ")")
MsgBox Av
End Sub

**royUK** · Aug 4th 2013

Re: macro for average of values -dynamic range of cells

Why use VBA?

In earlier versions create a Dynamic Named Range for the calculation

In later versions format the data as a Table, add a Total Row & set the calculation to Average.

Participate now!