Standard Deviation in VBA

ttbuson

Board Regular
Joined
Nov 18, 2011
Messages
80
I tried searching for similar threads, but couldn't quite find what I was looking for. I am well aware of Excel's capabilities when it comes to calculating standard deviation. However, I would like to code it myself in VBA. I can't get the below code to work and I have no idea why. Any help would be greatly appreciated. Thanks!

Code:
Function variance(numbers As Range) As Double
Dim i As Integer
Dim xbar As Double
Dim x As Double
xbar = WorksheetFunction.Average(numbers)
For i = 1 To numbers.Count
x = x + ((numbers.Value - xbar) ^ 2)
Next i
variance = x / numbers.Count
End Function
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
It's because of numbers.Value
It's trying to get a value for multiple number of ranges, which excel doesn't have the functionality.

The easiest but inefficient way to approach this is by looping through the range as an object.
Code:
Dim c As Range
For Each c in numbers
    x = x + ((c.Value - xbar) ^ 2)
Next c
variance = x / numbers.Count
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

You forgot to index the range:

Code:
    x = x + (numbers[SIZE=2][COLOR=#FF0000][B](i)[/B][/COLOR][/SIZE].Value - xbar) ^ 2
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi kpark91

The complete syntax is

Code:
    x = x + (numbers.Item[COLOR=#000000][SIZE=2](i)[/SIZE][/COLOR].Value - xbar) ^ 2

but since .Item() is a default property of the Range object for indexing I omitted it.

This is not something I like. There should be no default properties, to avoid confusions. The truth is that I was a bit lazy. :(
 

ttbuson

Board Regular
Joined
Nov 18, 2011
Messages
80
Holy moly this was useful, thanks guys! Now I feel like I have to go rewrite all my other code to make it more efficient...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top