Forum Discussion

Hr's avatar
Hr
Copper Contributor
Oct 20, 2022
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 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=fLtO44

 

 

excel
Formulas and Functions
office 365
  • JKPieterse's avatar
    JKPieterse
    Oct 20, 2022
    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,""))

8 Replies

Sort By
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 20, 2022
    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.
    • Hr's avatar
      Hr
      Copper Contributor
      Oct 20, 2022
      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?
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Oct 20, 2022
        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"))

Resources