#DIV/0 error when getting the average

[L40]

I have 2 work books, book1 and book2. I need help with a formula. When I have data in the cell it works great, but when there is no data I’m getting a #DIV/0 error.
Book 1 has this formula in it, but it is referencing book 2 that has multiple sheets in it. Example sheet1 through sheet 7
This is the formula I’m using.
=SUM('[book2.xlsx]sheet1:sheet7'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2)
Once again it works great when there is data. When no data is present I get #DIV/0 error. I have tried the following.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=IF('[book2.xlsx]sheet1:sheet7'!$B$65=0,0,SUM('[book2.xlsx]sheet1:sheet7'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2))
With this formula I get #REF error. If I select just 1 sheet from the example above it works fine =IF('[book2.xlsx]sheet1'!$B$65=0,0,SUM('[book2.xlsx]sheet1:sheet2'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2))
I need the cell to be blank or 0 if there is no data. I hope I haven’t confused any one.
Thanks

 

[jasonb75]

Untested

=IF(SUM('[book2.xlsx]sheet1:sheet2'!$B$65)=0,0,SUM('[book2.xlsx]sheet1:sheet2'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet2'!$B$65,0),2))

Or as long as you don't need it to work with excel 2003 (or older)

=IFERROR(SUM('[book2.xlsx]sheet1:sheet7'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2),0)

 

[Aladin Akyurek]

I have 2 work books, book1 and book2. I need help with a formula. When I have data in the cell it works great, but when there is no data I’m getting a #DIV/0 error.
Book 1 has this formula in it, but it is referencing book 2 that has multiple sheets in it. Example sheet1 through sheet 7
This is the formula I’m using.
=SUM('[book2.xlsx]sheet1:sheet7'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2)
Once again it works great when there is data. When no data is present I get #DIV/0 error. I have tried the following.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=IF('[book2.xlsx]sheet1:sheet7'!$B$65=0,0,SUM('[book2.xlsx]sheet1:sheet2'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2))
With this formula I get #REF error. If I select just 1 sheet from the example above it works fine =IF('[book2.xlsx]sheet1'!$B$65=0,0,SUM('[book2.xlsx]sheet1:sheet2'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2))
I need the cell to be blank or 0 if there is no data. I hope I haven’t confused any one.
Thanks

Try...

=SUM('[book2.xlsx]sheet1:sheet7'!$B$65)/MAX(1,INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2))

 

[T. Valko]

Untested

=IF(SUM('[book2.xlsx]sheet1:sheet2'!$B$65)=0,0,SUM('[book2.xlsx]sheet1:sheet2'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet2'!$B$65,0),2))

Or as long as you don't need it to work with excel 2003 (or older)

=IFERROR(SUM('[book2.xlsx]sheet1:sheet7'!$B$65)/INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet7'!$B$65,0),2),0)

COUNT would be a "safer" choice.

-10, 10 = 0

 

[T. Valko]

COUNT would be a "safer" choice.

-10, 10 = 0

Ooops!

Disregard. I now see that they want to exclude 0 values.

INDEX(FREQUENCY('[book2.xlsx]sheet1:sheet2'!$B$65,0),2)

 

[L40]

Thanks everyone for the awesome input. It is working great

 

[Aladin Akyurek]

Thanks everyone for the awesome input. It is working great

Happy to hear that. Thanks for the feedback.