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
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
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
AHA! Awesome, thank you!
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,)
