Forum Discussion
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,""))
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,""))
=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!