Hi
I am trying to find the two modes in a bimodal list of numbers. The MODE
function in Excel only reports the lowest one it finds. After a reply
from Tom I tried to use the FREQUENCY function but couldn't get it to
work. I was trying to use it with an array Arr().
Does it only work with ranges ?
I have also noticed that that the CountIf function doesn't seem to work
with an array. Am I doing something wrong ?


If AA.Count(Arr()) 0 Then
Shd.Cells(416, i) = AA.Count(Arr())
Shd.Cells(417, i) = AA.Sum(Arr())
Shd.Cells(418, i) = AA.Average(Arr())
Shd.Cells(419, i) = AA.Median(Arr())
Shd.Cells(420, i) = AA.Mode(Arr())

The above statements all work. The statements below don't seem to work.


Shd.Cells(421, i) = AA.CountIf(Arr(), 1)

Shd.Cells(422, i) = AA.Frequency(Arr(), Shd.Range("E422:E429"))



End If

TIA

Andrew B

Frequency returns an array:

Sub Mode()
Dim varr(1 To 100)
Dim varr1(1 To 5)
Dim v
For i = 1 To 100
varr(i) = Int(Rnd() * 5 + 1)
If i < 5 Then
varr1(i) = i
End If
Next
v = Application.Frequency(varr, varr1)
Debug.Print TypeName(v), LBound(v, 1), UBound(v, 1), LBound(v, 1), UBound(v,
2)
For i = LBound(v, 1) To UBound(v, 1)
Debug.Print i, v(i, 1)
Next
End Sub

--
Regards,
Tom Ogilvy

"Andrew B" wrote in message
...
Hi
I am trying to find the two modes in a bimodal list of numbers. The MODE
function in Excel only reports the lowest one it finds. After a reply
from Tom I tried to use the FREQUENCY function but couldn't get it to
work. I was trying to use it with an array Arr().
Does it only work with ranges ?
I have also noticed that that the CountIf function doesn't seem to work
with an array. Am I doing something wrong ?


If AA.Count(Arr()) 0 Then
Shd.Cells(416, i) = AA.Count(Arr())
Shd.Cells(417, i) = AA.Sum(Arr())
Shd.Cells(418, i) = AA.Average(Arr())
Shd.Cells(419, i) = AA.Median(Arr())
Shd.Cells(420, i) = AA.Mode(Arr())

The above statements all work. The statements below don't seem to work.


Shd.Cells(421, i) = AA.CountIf(Arr(), 1)

Shd.Cells(422, i) = AA.Frequency(Arr(), Shd.Range("E422:E429"))



End If

TIA

Andrew B

Thanks for your help once again Tom.

Andrew Bourke