AverageIf #DIV/0! error

    My current formula is: =AVERAGE('Week 1'!G4,'Week 2'!G4,'Week 3'!G4,'Week 4'!G4,'Week 5'!G4). When there is no Data entered on any one of the Week1-5 sheets I'm obviously getting the #DIV/0! error.



    I thought something like one of these would do it but I just cannot figure out what I'm missing here.


    =AVERAGEIF('Week 1'!G4,'Week 2'!G4,'Week 3'!G4,'Week 4'!G4,'Week 5'!G4, ">0")


    =IfError(AVERAGEIFS('Week 1'!G4,'Week 2'!G4,'Week 3'!G4,'Week 4'!G4,'Week 5'!G4,)

    Re: AverageIf #DIV/0! error


    How about wrapping your original formula with IFERROR?


    [COLOR="#0000FF"]=IFERROR(AVERAGE('Week 1'!G4,'Week 2'!G4,'Week 3'!G4,'Week 4'!G4,'Week 5'!G4),0)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: AverageIf #DIV/0! error


    Maybe spoke too soon, I don't get the error but I don't get results either. Instead of the #DIV/0! error I get 0.00%. Same problem really

    Re: AverageIf #DIV/0! error


    I may not be explaining it correctly. I don't want to hide the error or just show zero, I want to get an average regardless of whether or not there is data in every cell. So if I only have data for Weeks 1-4 and not Week 5, I still want the formula to give me an average. Does that make sense?

    Re: AverageIf #DIV/0! error


    You should only be getting an error if none of the cells contain values, or one of them contains an error.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: AverageIf #DIV/0! error


    Try this:


    Enter your sheet names in a separate range of cells, say X1:X5, then apply formula:
    [COLOR="#0000FF"]
    =SUMPRODUCT(SUMIF(INDIRECT("'"&$X$1:$X$5&"'!G4"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$X$1:$X$5&"'!G4"),">0"))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: AverageIf #DIV/0! error


    =SUMPRODUCT(SUMIF(INDIRECT(""'Week 1'!:'Week 5'"!G4"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT(""'Week1'!:'Week5'"!G4"),">0"))



    Not too advanced as far as Excel goes so I really appreciate the help but I attempted this and I got a message that the formula contains an error.

    Re: AverageIf #DIV/0! error


    That's not the right syntax. It would have to be:


    =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Week 1","Week 2","Week 3","Week 4","Week 5"}&"'!G4"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Week 1","Week 2","Week 3","Week 4","Week 5"}&"'!G4"),">0"))


    Note that this will still give you a DIV/0 error if none of the cells have a value, just like the original formula.


    Also, please take a minute to read the forum rules on cross-posting and follow them. Thanks.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: AverageIf #DIV/0! error


    When I said to enter the names of the sheets in separate range of cells I meant to actually enter "Week 1" in X1, "Week 2" in X2, "Week 3" in X3 and so on. This way will allow more flexibility instead of revising the formula when you change sheetnames. You can even make it more flexible by creating a dynamic named range and using that as a reference so you can add/remove sheets at will.


    Again, to avoid the DIV/0 wrap the whole thing in an IFERROR() function.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login