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,""))
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.
=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?
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?