vba arrays, count blank cells
 

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

vba arrays, count blank cells
 

Hi Matt,

A nasty formula and a simple UDF -

=(COUNT(ref)+COUNTIF(ref,""))/(SUMPRODUCT(1/(((ref))*(ref0)+(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" wrote in message
ups.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

vba arrays, count blank cells
 

matt wrote:
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?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayCount(arr) - ArrayCountIf(arr, 0, "")

Alan Beban