Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Averages in VBA...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Averages in VBA...
Can I do this without populating worksheet cells with the Average and
Standard Deviation directly in VBA? Yes. Dim dX As Double dX = Application.WorksheetFunction.Average(Sheets("Shee t1").Range("A1:A2")) HTH, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Averages in VBA...
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Averages in VBA...
Thanks. That's exactly what I needed. I never realized that
Worksheetfunction functionality existed. "Jeff Harbin" wrote in message ink.net... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating averages for subgroups | Excel Discussion (Misc queries) | |||
Calculating weighted averages | Excel Discussion (Misc queries) | |||
calculating averages | Excel Discussion (Misc queries) | |||
calculating averages | Excel Worksheet Functions | |||
calculating averages | Excel Programming |