Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA function for "Mean" using Array as argument
I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use an equation such as: =RSD(values), where the values are cell references (a variable quantity) chosen by the user. To do this I've copied scripts for "Mean" and "StdDev", then wrote a small function for RSD that calls those two. When I try to implement RSD in the worksheet (or even just Mean), the cell returns an error: #VALUE! I think the problem has something to do with the Arr() variable used as the argument, as I've tried some simpler functions with array arguments and can't get them to work, but using standard non-array arguments works fine. I can write a subroutine that calls the functions and displays the correct result, but I would like to be able to use RSD as a typical function in my worksheet. If I could get Mean to work properly, I should be able to apply that knowledge to StdDev and RSD. The text for Mean is listed below. I've tried quite a few modifications to try to make it work, so if someone could get it to work and reply with the correct function text (or with another function that performs similarly), I'd appreciate it. Thanks. Function Mean(Arr() As Single) Dim Sum As Single Dim i As Integer Sum = 0 For i = 1 To UBound(Arr) Sum = Sum + Arr(i) Next i Mean = Sum / UBound(Arr) End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Sumproduct + Array Function? | Excel Worksheet Functions | |||
Code to determine if a cell contains an array function? | Excel Discussion (Misc queries) | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions |