I have a row of numbers and I want to calculate the average and standard deviation of these numbers using the excel functions within a macro. I don't want to output the results, I need it for a loop within the macro.

What is the code for this type of operation?

Thanks,

James

0788 1546 946

Hello James

Thank you for your post.

We do not have any VBA trainers available currently to assist with your request as they are all training today.

I can refer your question to a VBA trainer for review, however you may not receive a response until sometime next week.

Amanda

Hi James

Thank you for your question

The following code will solve your problem for you

Sub ElemStats()

Dim dblMean As Double
Dim dblStDEv As Double


dblMean = Application.WorksheetFunction.Average(Range("b2:e2"))

MsgBox dblMean

dblStDEv = Application.WorksheetFunction.StDev(Range("b2:e2"))

MsgBox dblStDEv

End Sub

The critical lines of code use the worksheet function method of the application object to calculate the arithmentic mean and the standard deviation of a row of numbers in the range "b2:e2".

I have used msgboxes to output the results, but this is obviously not essential and you can just manipulate the variables as you wish

Regards

Stephen