CODEWITHSUNDEEP
excelvba
Alan Treanor
Alan Treanor

Reputation: 159

average if greater than zero vba

I am trying to average a column but only if the value is greater than zero. I then want it to put the information in the next blank cell in that row.

The below code was working as a simple Average but I want it to exclude any values of zero from the above cells. 

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

I Tried with the following code to have it as if the cell address is greater than zero. But it keeps giving me an error to debug?

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

Upvotes: 3

Views: 2507

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

Your syntax for the formula is wrong.

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

Upvotes: 5

Related Questions