VBA AVERAGE formula, ignoring zeros
 

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?


---
Message posted from http://www.ExcelForum.com/

VBA AVERAGE formula, 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



--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Guy Hoffman " skrev i en
meddelelse ...
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?


---
Message posted from http://www.ExcelForum.com/

VBA AVERAGE formula, ignoring zeros
 

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

--
Message posted from http://www.ExcelForum.com

VBA AVERAGE formula, ignoring zeros
 

You're welcome.

LeoH

"Guy Hoffman " skrev i en
meddelelse ...
Hard to believe the countif function is so limited (i.e., cannot use on
3D ref). Thanks for the code. I'll give it a try.

GH

VBA AVERAGE formula, ignoring zeros
 

You could use the database worksheet function DAVERAGE

"Guy Hoffman " wrote in message
...
The problem is that the formula as written includes zeros in the
average. I need to ignore zeros.

VBA AVERAGE formula, ignoring zeros
 

I couldn't get the DAverage function to work if the database was a 3D
reference


---
Message posted from http://www.ExcelForum.com/