#VALUE error when using custom formula for standard deviation

Question

Let me start off by explaining the problem I am trying to solve and then describe the error I'm getting. I have two columns of data where the second column is my data set and the first column is the number of times the matching number in the second column appears in the set of data. Here is an example:

The data set I want to get a standard deviation of: 3, 3, 5, 4, 6, 7, 2, 3, 4.  So my spreadsheet would look like the following:

3	3
1	5
2	4
1	6
1	7
1	2

So there are three 3s, one 5, two 4s, etc... in the data set. (my real data set is ~100k values). I couldn't find a built-in function to use both columns to calculate the standard deviation so I decided to try writing my own just for this little project. Here's my first shot:

---

'Calculates the standard deviation of a set of numbers

Function StdDevSpecial(ByVal range1 As range, ByVal range2 As range) As Double

Call Application.Volatile(True)

'Use SampleSet as an array to store all of the values in to perform the standard deviation calculation upon

Dim SampleSet() As Double

Dim index As Integer

Dim p As Integer

Dim i As Integer

Dim j As Integer

For i = 1 To range1.Rows.Count

p = CInt(range1(i, 1))

For j = 1 To p

SampleSet(index, 1) = range2(i, 1)

index = index + 1

Next j

Next i

StdDevSpecial = WorksheetFunction.StDev_P(SampleSet)

End Function

---

I declared the integers and used the "CInt" function as troubleshooting because when I use the function on the example set of data above, I get a #VALUE! error saying "A value used in the formula is of the wrong data type." The correct answer should be 1.523. Any ideas? Thank you!

Answer 1

Check out the acceptable value range of the integer data type.  It is less than your 100K values.

Here's a UDF that I think will do what you want:

==========================

Option Explicit

Function StdDevSpecial(rMult As Range, rVal As Range) As Double

    Dim c As Range

    Dim i As Long, j As Long

    Dim v() As Double

If rMult.Columns.Count <> 1 Or rVal.Columns.Count <> 1 Then

    MsgBox ("Ranges must be single columns")

    Exit Function

End If

If rMult.Rows.Count <> rVal.Rows.Count Then

    MsgBox ("Ranges must be the same size")

End If

With WorksheetFunction

    ReDim v(1 To WorksheetFunction.Sum(rMult))

    j = 1

    For i = 1 To rVal.Rows.Count

        For j = j To j + rMult(i) - 1

            v(j) = rVal(i)

        Next j

    Next i

    StdDevSpecial = .StDevP(v)

End With

End Function

================================

Edit:  Worksheet function solution

By the way, I think the following will also give the same result as STDEVP, assuming that your data is in A1:B100000 laid out as above.

=SQRT(SUMPRODUCT($A$1:$A$100000,($B$1:$B$100000-SUMPRODUCT(

$A$1:$A$100000,$B$1:$B$100000)/SUM($A$1:$A$100000))^2)/SUM($A$1:$A$100000))