Peter T
12/14/2006 9:52:00 PM
Hi Matt,
A nasty formula and a simple UDF -
=(COUNT(ref)+COUNTIF(ref,""))/(SUMPRODUCT(1/(((ref))*(ref>0)+(ref<=0)))-SUMP
RODUCT(--(ref<=0)))
Would be less than half that merely to cater for -ve's, but those dam zeros
& blanks!
Function HarmeanAny(ref As Range) As Double
Dim vArr, v
On Error GoTo errExit
vArr = ref.Value
For Each v In vArr
If IsNumeric(v) Then
If v > 0 Then
t = t + (1 / Val(v))
End If
End If
Next
HarmeanAny = ref.Count / t
errExit:
Harm = CVErr(xlValue)
End Function
Could have done -
Dim cel as range
For each cel in ref, etc
but you you asked about getting your own array from a range, which
incidently is always 2d even if a single row or column.
Regards,
Peter T
"matt" <meh2030@gmail.com> wrote in message
news:1166127523.190072.299370@73g2000cwn.googlegroups.com...
> I am trying to write a harmonic mean function that will evaluate a set
> of numbers, even if there are negative or zero values.
>
> The harmonic mean formula is the count of numbers in the data set
> divided by the sum of 1/n. The excel function is =harmean(). For
> example, the data set 5, 7, 4, 10, 11, 15, 12 yields a harmonic mean of
> 7.497...( 7 / (1/5+1/7+1/4+1/10+1/11+1/15+1/12)). This formula does
> NOT work if their is a zero OR negative number in the set.
>
> I have written code that will evaluate the numbers, but my question is
> whether or not their is a way to count the blank/text/empty cells in an
> array? Originally, I thought using a countif(array,">=0") or
> count(isempty(array)) would work, but learned that countif doesn't seem
> to work with arrays and count(isempty(array)) will return 1 no matter
> how many cells may be empty or will return zero if no cells are empty.
> Any help would be appreciated.
>
> If the above data set were 5, 7, -4, 10, -11, 15, and 12 the formula
> would be
> ( 7 / (1/5+1/7+1/10+1/15+1/12)).
>
> Note: I know that in the code below I could use (counter / sum(array)),
> but I'm looking to see if there is a more robust way.
>
> Sub hMeanRefined()
>
> Dim hMean()
> Dim counter
> Dim shM 'sum hMean
> Dim chM 'count hMean
> Dim cehM 'count empty hMean
> Dim vhM 'value hMean
>
> Sheets("Refined").Select
>
> Rows("2:8").ClearContents
> counter = Range("a1").CurrentRegion.Columns.Count
>
> ReDim hMean(1, 1 To counter)
>
> For a = 1 To counter
> If Cells(1, a) <= 0 Then
> hMean(1, a) = "empty"
> 'hMean(1, a) = Empty
> Cells(2, a) = hMean(1, a)
> Else
> hMean(1, a) = 1 / Cells(1, a)
> Cells(2, a) = hMean(1, a)
> End If
> Next
>
> shM = Application.Sum(hMean)
> Cells(2, counter + 1) = shM
> Cells(2, counter + 2) = "Sum of 1/n"
>
> chM = Application.Count(hMean)
> Cells(3, counter + 1) = chM
> Cells(3, counter + 2) = "Count of numbers in set"
>
> 'cehM = Application.CountIf(hMean, ">=0")
> 'cehM = Application.Count(IsEmpty(hMean))
>
> cehM = Application.CountIf(Range(Cells(1, 1), Cells(1, counter)),
> "<=0")
> Cells(4, counter + 1) = cehM
> Cells(4, counter + 2) = "Count of empty or negative cells in set"
>
> vhM = (chM + cehM) / shM
> Cells(5, counter + 1) = (Cells(3, counter + 1) + Cells(4, counter +
> 1)) / Cells(2, counter + 1)
> Cells(5, counter + 2) = "(Count of numbers + Count of empty) / Sum of
> 1/n"
>
> Cells(6, counter + 1) = vhM
>
> End Sub
>
> Thanks,
>
> Matt
>