1

I have the following formula returning #DIV/0! when all 5 cells are blank:

=AVERAGEIF(CV17:CV21,">0")

Spreadsheet image

This is being used to average call time over the period of a 5 day working week so I am using the AVERAGEIF function to only count cells that I enter a time into. The formula is in cell CV24.

As I want another cell to calculate the rolling average over a year I need CV24 to display 0 if no times are entered for the entire week.

I have attempted to use the IFERROR as such: 

AVERAGEIF(CV17:CV21,">0",IFERROR(CV17:CV21,0))

This is telling me I have typed an incorrect formula.

I also tried =IFERROR(CV10:CV14,0) which returned 0 when times were entered.

I feel like I'm close but my understanding of formulas isn't great. Please help!

PeterH
  • 7,377
  • 20
  • 54
  • 82
Ben Nicholas
  • 15
  • 1
  • 6

1 Answers1

3

AVERAGEIF(CV17:CV21,">0",IFERROR(CV17:CV21,0))

This is your formula

IFERROR(AVERAGEIF(CV17:CV21,">0",CV17:CV21),0)

This is the correct use of the IFERROR formula.

Scott Craner
  • 22,693
  • 3
  • 21
  • 25
Serkan BAYKAL
  • 86
  • 2