Solved: How to get AVERAGE of range into a cell

[DaveK]

I need some help on figuring out how to determine the AVERAGE of a range of values, and get that AVERAGE value into a specific cell.

the variable Month is an INTEGER


Range("J2").Offset(RowOffset:=1, ColumnOffset:=1).Resize(Month).Select



Now what I would like to do is this:

Cells(3, "C") = Average.Selection


I know that isnt the way to do it... but you can get the idea.

How can I do this in VBA?
Dave

[Bob Phillips]

Range("C3").Value = Application.Average(Range("J2").Offset(1, 1).Resize(Month))


Not the best idea to use Month as a variable name, seeing as it is a VBA function.

[DaveK]

Amazing... you are really great!

I continue to learn this... and I am kinda wondering how that works... after researching the Application object, searching for AVERAGE... it wasn't there... yet I realise now... that this is really what you are doing.


Range("C3").Value = Application.WorksheetFunction.Average(Range("J2").Offset(1, 1).Resize(Month))


How is it that we can leave off the WorksheetFunction part ? Curious.

THANKS AGAIN!
Dave

[Bob Phillips]

Because the Application is Excel, and Average is part of the Application. IT is also in the WorksheetFunctions collection, but oddly, not all are, and error handling is different with Application and WorksheetFunction.