VBA AVERAGE formula, ignoring zeros

  • Thread starter Thread starter Guy Hoffman
  • Start date Start date
G

Guy Hoffman

I have the following code:


'Average applicable cells ignoring zeros
Worksheets("Summary").Range("H5").Formula = "=Average('" &
startsheet & ":" & endsheet & "'!H5)"


The problem is that the formula as written includes zeros in the
average. I need to ignore zeros.

Some background:

This is only a portion of a subroutine

The routine then goes on to copy the newly writtem formula (note the
relative fererence) to other selected cells on the sheet "Summary"

The "startsheet" and "endsheet" strings used in the formula can change
depending on which sheets are specified by the user so I need to keep
this logic

Can anyone help we with a formula to average the range specified,
ignoring zeros?
 
Guy

It will take an array formula to get the average, and this
3-d-kind of an array formula is not possible in Excel (I believe).

Maybe you can use part of the below code

Sub TestAverage()
'Leo Heuser, 10 Jan. 2004
Dim Ave As Double
Dim Counter As Long
Dim EndSheet As String
Dim Index1 As Long
Dim Index2 As Long
Dim Sh As Worksheet
Dim SheetArray As Variant
Dim StartSheet As String
Dim Total As Double

StartSheet = "Sheet4"
EndSheet = "Sheet12"


Index1 = Sheets(StartSheet).Index
Index2 = Sheets(EndSheet).Index

SheetArray = _
Application.WorksheetFunction. _
Transpose(Evaluate("Row(" & Index1 & ":" & Index2 & ")"))

For Each Sh In Sheets(SheetArray)
With Sh.Range("H4")
If .Value <> 0 And .Value <> "" Then
Counter = Counter + 1
Total = Total + .Value
End If
End With
Next Sh

Ave = Total / Counter

MsgBox Ave
End Sub
 
Hard to believe the countif function is so limited (i.e., cannot use o
3D ref). Thanks for the code. I'll give it a try.

G
 
Back
Top