# 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

[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

AHA! Awesome, thank you!

• 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

Just change the last argument to return a blank.

=IFERROR(AVERAGE('Week 1'!G4,'Week 2'!G4,'Week 3'!G4,'Week 4'!G4,'Week 5'!G4),"")

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• 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.

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!