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
Alan treanor
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
chris neilsen
source
to share