Divide by zero error

[rutica]

Hi,

I am using Excel 2010. I have an array that calculates an average based on certain criteria. Everything works fine except I get #DIV/0! if there is no data.

My array is:

{=AVERAGE(IF(ALL!B:B=B1,
IF(ALL!D:D=B4,
IF(ALL!E:E=C4,
IF(DATE(YEAR(ALL!F:F),MONTH(ALL!F:F),1)=D3,
ALL!H:H)))))}

Please see attached example. There are 2 tabs. The raw data is on the ALL tab. The array formulas with the errors are on the "Compliance" tab, row 4. (I have only filled in row 4 so far, but eventually I will populate the other 7 rows with formulas).

Help!
Also, I noticed the spreadsheet has gotten very slow. I suppose this is due to the array, but is there any way to fix that?

Thanks!

[Tony Valko]

Don't use entire columns as range references in array formulas. Use smaller specific ranges.

Array entered**:

=IFERROR(AVERAGE(IF(ALL!B3:B20=B1,IF(ALL!D3:D20=B4,IF(ALL!E3:E20=C4,IF(ALL!F3:F20-DAY(ALL!F3:F20)+1=D3,ALL!H3:H20))))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[TMS]

You're using 2010 so add an IFERROR, thus:

{=IFERROR(AVERAGE(IF(ALL!B:B=B1,
IF(ALL!D:D=B4,
IF(ALL!E:E=C4,
IF(DATE(YEAR(ALL!F:F),MONTH(ALL!F:F),1)=D3,
ALL!H:H))))),0)}


I would not recommend using full column ranges for your calculations. They are noticeably slow as each range has over a million cells.


Regards, TMS

[rutica]

Tony Valko and TMShucks:

Thanks to both of you. I took your advice and reduced the range reference and I used the IFERROR and everything works great!

Thanks thanks thanks thanks!!!

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Tony Valko]

You're welcome. Thanks for the feedback!