Forum Discussion
Solved
Averageifs function divide by zero error
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 commu...
- 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,""))
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?
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"))
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"))
JKPieterse 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.
- 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