Chi squared statistic function

Hi!

First post so I hope this is okay.

I'm trying to write a VBA function which performs the chi squared test (a simple statistical test to compare proportions).

It works (roughly) by comparing the difference between observed values for cells in (say) a two-by-two grid, and the expected values for those cells (calculated by multiplying the row total by the column total and dividing by the overall total).

I hope to get it to work without having to calculate the expected values as set out in the CHITEST Excel function.

I’m trying to convert over code which works in ASP, but I don’t think I’m calling it correctly, as I keep getting #VALUE! or #NUM!

Here's the code...

Function NewChi(a As Integer, b As Integer, c As Integer, d As Integer)

Dim N, ea, eb, ec, ed, chival As Integer
    N = a + b + c + d
   ea = ((a + c) * (a + b)) / N
   eb = ((b + d) * (a + b)) / N
   ec = ((a + c) * (c + d)) / N
   ed = ((b + d) * (c + d)) / N
  chival = (((a - ea) ^ 2) / ea) + (((b - eb) ^ 2) / eb) + (((c - ec) ^ 2) / ec) + (((d - ed) ^ 2) / ed)
  
NewChi= chival

End Function

...and an example's attached.

Thanks!

Iain

Using Integer you get overflow. Use Doubles.

Function NewChi(a As Double, b As Double, c As Double, d As Double) As Double
    Dim N       As Double
    Dim ea      As Double
    Dim eb      As Double
    Dim ec      As Double
    Dim ed      As Double

    N = a + b + c + d
    ea = ((a + c) * (a + b)) / N
    eb = ((b + d) * (a + b)) / N
    ec = ((a + c) * (c + d)) / N
    ed = ((b + d) * (c + d)) / N
    NewChi = (((a - ea) ^ 2) / ea) + _
             (((b - eb) ^ 2) / eb) + _
             (((c - ec) ^ 2) / ec) + _
             (((d - ed) ^ 2) / ed)
End Function

Also, in this declaration,

Dim N, ea, eb, ec, ed, chival As Integer

... everything but chival is declared as a Variant.

You can do this,

Dim N As Variant, s As String, d As Double, ...