CODEWITHSUNDEEP
excelif-statementcountsumaverage
user2208691
user2208691

Reputation: 51

How to make an average formula only count numbers greater than zero?

=AVERAGE(K2:K32)

I have this formula spitting out an average for coulmn K as you can see. The problem I am having is that the numbers in column K are populated by a formula basically adding two other columns together so if no data is entered the number returned is 0. This obviously throws off an average calculation for a month that is not complete, how can I make this formula only count the numbers which are greater than zero?

Upvotes: 5

Views: 61684

Answers (3)

pnuts
pnuts

Reputation: 59495

Since you mention an average calculation for a month that is not complete and that the numbers in column K are populated by a formula basically adding two other columns together if either of the numbers being added could be negative it seems possible that the formula may return 0 even for that part of the month that is complete, so strictly speaking a 0 in that case should be taken into account for a true average. Hence, assuming one of the numbers being added is in ColumnE (which will be blank or 0 for the incomplete part of the month) I suggest an array formula such as: 

 =AVERAGE(IF(E2:E32<>0,K2:K32))

entered with Ctrl+Shift+Enter. For months with less than 31 days enter a value other than 0 into ColumnE for the non-existent days.

Upvotes: 0

SFarrell
SFarrell

Reputation: 1

You must use:

=AVERAGEIF(K2:K32,">0")

Make sure there are no spaces in the formula.

Upvotes: -1

shahkalpesh
shahkalpesh

Reputation: 33484

=AVERAGEIF(K2:K32, ">0")

Here is the example formula, which includes cells having value > 0.

Upvotes: 11

Related Questions