Averageifs: what do I do wrong? It's always the Blanks that cause problems!!

[Hans Troost]

Hi all,

In a spreadsheet with I have i column computing the average of the 3 preceding columns. I filled only a few rows at the top, the others will be filled during time (1 row per day).
Of course I already formatted the whole sheet and every row is filled with the Average-formula.

Because I am aware of 0's and blanks I use the Averageifs-function to avoid problems, but no: still #DIV/0!'s in the colums.

The formula is: =ROUND(AVERAGEIFS(B7:D7;B7:D7;"<>0";B7:D7;"<>""");1)

It seems to work for rows where only one of the columns is filled: then it ignores the blanks in the other 2. But if all 3 are blank: doesn't work.

Do I do something wrong?

It shows like this:

and this is the spreadsheet.

Thanks in advance for any help!

Regards,
Hans Troost

 

[Andrew Poulsom]

How about?

=IFERROR(ROUND(AVERAGEIFS(B7:D7;B7:D7;"<>0";B7:D7;"<>""");1);0)

 

[Hans Troost]

Thanks, I will try and it expect it to work, but why (why, why) doesn't the Averageifs-function as I use it do the job?
Why do we always have to fight against blanks (and zero's) in XL-formula's. Ok: I shouldn't ask you (nor here) but sometimes I get really tired of it....

 

[Rada1]

It's not excel, it's mathematics. You can't divide by zero.

 

[Hans Troost]

Thanks again Andrew,

Due to your recommendation (the iferror) I also left out the IFS from Averageifs and decided to simplify it to =IFERROR(ROUND(AVERAGE(B3:D3);1);"").

The spreadsheet now works as I want. And yes, I agree with Rada1 that indeed it is math's not XL the dividing by 0, but I still doubt about the handling of blanks (= empty cells)

Best regards,

Hans

 

[Jonmo1]

It handled the blanks just fine if there is at least 1 number in the range.

The reason for the Div/0 is because all 3 cells were blank (as you said).
If all 3 are blank, then there are no numbers to average..

Average is SUM devided by COUNT.
If there are no numbers in the range, then the SUM is 0, and the count is also 0, so it's 0 devided by 0 = #Div/0!

 

[Hans Troost]

Thanks Jonmo1,

I understand now, thanks to your explanation and I will take it into account in future (similar) formula-problems.

Hans

 

[Jonmo1]

You're welcome..


Also..

Average / AverageIF / AverageIFS will ignore blanks and text by default anyway, so there's no need to include that criteria...
So you only need to use NOT=0 as a criteria..
And only 1 criteria means AveragIF instead of ifS

Try
=IFERROR(ROUND(AVERAGEIF(B7:D7;"<>0");1);0)