SOLVED

Averageifs function divide by zero error

Copper Contributor

I trying to get the correct average of just the q1 and q3 values using averageifs function. Have looked a lot online but couldn't find a solution. However, someone kindly did help me here on MS community with a formula that works: =AVERAGE(IF(H2:H13<>"", IF(H2:H13<>0, IF((G2:G13="q1")+(G2:G13="q3")>0, H2:H13)))). They also explained that it is an OR logic type scenario.

But I am hoping to use the averageifs function (or a much smaller formula/function) to get the answer since by definition that function should work with multiple criteria even in the same column.

Some more info that may be helpful before answering my question:

Version: O365 Excel

The blank cells in the average range are empty with no values.

I have already tried this formula: =AVERAGE(AVERAGEIFS(H2:H13,G2:G13,{"q1","q3"})), but I was told it does not always work (for eg. if a value is added to any of the blank cells, the average comes incorrect).

Here is the link (because I don't have the permission to upload the images here directly and don't know how to) to the screenshot of my sheet from my OneDrive: https://1drv.ms/u/s!AmNyoq6I8cL6iWqAoPs7zfdjWREs?e=IcIoJ7

 

 

8 Replies
The AVERAGEIFS function always uses AND, so all conditions in the function must be met, for a value to be counted in. To have an OR condition, you simply add two AVERAGEIFS functions:
=AVERAGEIFS(H2:H13,G2:G13,"q1")+AVERAGEIFS(H2:H13,G2:G13,"q3")
The formula will return #DIV/0! if q1 and/or q3 is not in your data.
Hello Jan Karel Pieterse,
Thanks for your reply, now the averageifs function makes sense to me. However, I got the correct average by making some changes in your formula: =(AVERAGEIFS(H2:H13,G2:G13,"q1")+AVERAGEIFS(H2:H13,G2:G13,"q3"))/2.
Do you know if there is simpler formula/function to compute such a problem (say with more than 2 criteria) or know somewhere I can find/ask?
Ouch, I treated this as if it were a SUMIFS, it's an AVERAGEIFS. Those -of course- cannot simply be added :-). I'm also not convinced your divide two is valid.
I see two alternatives:
=AVERAGE(IF((A1:A44="q1")+(A1:A44="q2")>0,B1:B44,""))
or
=(SUMIFS(B1:B44,A1:A44,"q1")+SUMIFS(B1:B44,A1:A44,"q2"))/(COUNTIFS(A1:A44,"q1")+COUNTIFS(A1:A44,"q2"))

@Jan Karel Pieterse perhaps you can try divide by two yourself since I am indeed getting the correct average with that but just think the formula is too long. Also, after trying your two alternatives:

1. =AVERAGE(IF((G2:G13="q1")+(G2:G13="q3")>0,H2:H13," "))

2. =(SUMIFS(H2:H13,G2:G13,"q1")+SUMIFS(H2:H13,G2:G13,"q3"))/(COUNTIFS(G2:G13,"q1")+COUNTIFS(G2:G13,"q3"))

I get 18,847 for both, instead of the correct average which is 56,540.5.

best response confirmed by Hr (Copper Contributor)
Solution
That is due to the empty cells in your data, to cater for those we need an additional check:
=AVERAGE(IF((H2:H13<>"")*((G2:G13="Q1")+(G2:G13="Q3"))>0,H2:H13,""))

Ok this one works. And I now see why you are not convinced about the divide by two formula. Turns out, it returns the wrong answer if I enter a value in one of the blank cells say 123456 in H3. Just still wish there was a much shorter formula to compute this

I'm afraid not! There might be alternatives, but I doubt if those will be much simpler. You could create a LAMBDA function for this, but I doubt whether it is worth the effort:
https://jkp-ads.com/Articles/Excel-lambda-function-basics.asp
Ok will take a look, thanks!
1 best response

Accepted Solutions
best response confirmed by Hr (Copper Contributor)
Solution
That is due to the empty cells in your data, to cater for those we need an additional check:
=AVERAGE(IF((H2:H13<>"")*((G2:G13="Q1")+(G2:G13="Q3"))>0,H2:H13,""))

View solution in original post