Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average formula where blank cells are counted as zeros | Excel Worksheet Functions | |||
Average from three different coworkers excluding zeros formula? | Excel Discussion (Misc queries) | |||
Average ignoring Zeros | Excel Worksheet Functions | |||
Averaging, ignoring zeros | Excel Worksheet Functions | |||
Formula to average ignoring negatives? | Excel Discussion (Misc queries) |