Clever Geek Handbook

Average if greater than zero vba

I'm trying to average a column, but only if the value is greater than zero. Then I want it to put information in the next blank cell on that row.

The following code worked like a simple Average, but I want it to exclude any zero values โ€‹โ€‹from the above cells.

With Range("D2")
    .End(xlDown)(2, 1) = _
    "=AVERAGE(" & .Address & ":" & .End(xlDown).Address & ")"
End With

I have tried with the following code to have it as if the cell address is greater than zero. But it keeps giving me error for debugging?

With Range("D2")
    .End(xlDown)(2, 1) = _
    "=AVERAGEIF(" & .Address & ":" & .End(xlDown).Address & "," & Cell.Address & " > 0," & .Address & ":" & .End(xlDown).Address & ")"
End With

Any help would be great.

Thanks Al

+3


source to share


1 answer


Your syntax for the formula is incorrect.

You need to create a formula like

=AVERAGEIF(D2:Dxx, ">0")


So use this

With Range("D2")
    .End(xlDown)(2, 1) = _
    "=AVERAGEIF(" & .Address & ":" & .End(xlDown).Address & ","">0"")"
End With
+5


source






More articles:


All Articles