0

I have a 28 numbers, and every day in the month of February a new number will be added to the list. All the cells that haven't been filled in yet are zero (so at the start of the month the whole column was 28 zeroes). I'm looking for a method that will take the mean average of this range of cells but ignore any zero values.

Is there a way of specifying a range that meets a certain condition, in this case being greater than zero, so I don't have to manually change the range on which the average is calculated every day?

I'm looking for a formula only.

Community
  • 1
  • 1
Lou
  • 2,200
  • 2
  • 33
  • 66

1 Answers1

2

The first thing you should consider is if cells that have not been filled in yet actually have to be zero or not. Empty cells are ignored by the AVERAGE function and so this will work as expected.

Otherwise you will want an expression along the lines of:

=SUM(A1:A28) / COUNTIF(A1:A28, ">0")

where A1:A28 is your desired range. The COUNTIF function allows us to determine the number of non-zero entries in the range.

Freddie Witherden
  • 2,369
  • 1
  • 26
  • 41