Calculating Averages in VBA...

  • Thread starter Thread starter Jeff Harbin
  • Start date Start date
J

Jeff Harbin

I've got a calculation that I need to compute that includes the average and
the standard deviation of a group of numbers. The group of numbers will
vary in size from one instance to another so I want to programmically
calculate the values.

In the past I've written code that places the values of the Average and
Standard Deviation into cells on a separate worksheet and then reference
those cells in my VBA code....

////Example////
Dim DataRange as String

Worksheets("Sheet1").Range("A1").Formula = "=Average(DataRange)"
Worksheets("Sheet1").Range("A2").Formula = "=StDev(DataRange)"

......

to divide the standard deviation by the average, I'd write code similar
to....

Worksheets("Sheet1").Range("A2").Value/Worksheets("Sheet1").Range("A1").Valu
e

/////End Code//////

Can I do this without populating worksheet cells with the Average and
Standard Deviation directly in VBA?

Thanks,

Jeff
 
Can I do this without populating worksheet cells with the Average and
Standard Deviation directly in VBA?
Click to expand...

Yes.

Dim dX As Double
dX = Application.WorksheetFunction.Average(Sheets("Sheet1").Range("A1:A2"))

HTH,
Merjet
 
I am not quite sure if I am interpreting your query right hence I wil
try and provide 2 solutions. Hopefully, one of them would meet you
needs.

Firstly, it appears that the values for the average and the standar
deviation have already been calculated.

If that is the case, then store these values in transition variable
and then divide one by the other as follows :-

Dim myAverage as double, myStdDev as double, ratioOfTheTwo as double

myAverage = 'known value
myStdDev = 'known value
ratioOfTheTwo = myAverage/myStdDev


Secondly, if the above does not meet your needs, then to compute th
averages and standard deviation entirely in VBA, you can populate a
array say myVals() with these numbers (the numbers that you call th
group of numbers) and then do the following :-

myAverage = application.worksheetfunction.average(myVals)
myStdDev = application.worksheetfunction.stdev(myVals)

Make sure that the array is of exactly the same size as the number o
entries in your group of numbers because the empty elements in th
array would not be ignored and the result would be incorrect - lowe
than what you would expect.

I hope this is what you are looking for. If not, sorry, I interprete
your query wrong.


Best regards


Deepa
 
Thanks. That's exactly what I needed. I never realized that
Worksheetfunction functionality existed.
 
Back
Top