How to Calculate Standard Deviations in Excel Visual Basic
by ContributorExcel is the spreadsheet program included in the Microsoft Office Suite of productivity software. A spreadsheet program manages data through a series of tables. Each data value is placed within an individual cell within the table. Values can be manipulated through a set of built-in functions. Standard deviation is average deviation from the mean of the values of a set. Simply put, it identifies the precision of a set of values. Standard deviation can be calculated using the built-in function STDEV in Excel, or Visual Basic for Applications (VBA).
Built-in Standard Deviation Function
Open Excel and the data set where you want to find the standard deviation.
Type"=STDEV(" in the cell where you want to display the standard deviation. Do not press "Enter."
Holding down shift, select the range of cells containing the data points. If the cells are not continuous, hold down Ctrl while selecting the non-continuous cells. This should place a series of cells names in the formula bar after "=STDEV(".
Type ")" in the formula bar and press "Enter." The standard deviation will be calculated using the unbiased method.
Using Built-in Functions in VBA
This same built-in function can be used in a VBA script to calculate standard deviation. Open VBA and click on the end of the module where you are going to calculate the standard deviation.
Type "Function StDev( Rng As Range)" and then press "Enter." This should also create a new line "End Function."
On the line between "Function ..." and "End Function," type "StDev = Application.WorksheetFunction.StDev( Rng)" and then press "Enter."
To calculate the standard deviation in VBA, simply use the function StDev().
Tip
- check This same method can be used to access any of the built-in functions of Excel within VBA.
Items you will need
References
Photo Credits
- photo_camera BananaStock/BananaStock/Getty Images