#div/0! with a twist

**Bjordion** · 11-11-2011, 03:09 PM

I am receiving the typical #div/0! error. I know what is causing the error, but I would very much like to resolve the error message with a method other than utilizing an IF statement within the current function.

My reason for this preference is that the current formula that is causing the error message is already rather long (see below) and is applied to a very large number of cells, which requires up to 40 seconds to process in its entirety when an update is made. An IF statement that repeats a large portion of the formula would lengthen the process time even moreso.

=(SUMPRODUCT(SUMIFS('Cash Receipts'!$F:$F,'Cash Receipts'!$A:$A,Income,'Cash Receipts'!$B:$B,$A35,'Cash Receipts'!$I:$I,">="&C$1,'Cash Receipts'!$I:$I,"<="&C$2))-SUMPRODUCT(COUNTIFS('Cash Receipts'!$A:$A,Income,'Cash Receipts'!$B:$B,$A35,'Cash Receipts'!$I:$I,">="&C$1,'Cash Receipts'!$I:$I,"<="&C$2))*(EOMONTH(C$1,0)+1))/SUMPRODUCT(COUNTIFS('Cash Receipts'!$A:$A,Income,'Cash Receipts'!$B:$B,$A35,'Cash Receipts'!$I:$I,">="&C$1,'Cash Receipts'!$I:$I,"<="&C$2))

I realize a workaround would be to insert the IF(ISERROR... function into another column on the same spreadsheet and hide the column with the errors, but I'm holding out hope for some method that simplifies the function or at the very least does not require additional time to process the function.

Any help would be appreciated.

Cheers

Bjordion

**ConneXionLost** · 11-11-2011, 03:17 PM

The Excel version listed in your profile (Excel 1020?) might indicate you could use IFERROR instead of IF/ISERROR which, would save some real estate for you.

Also, your use of the three SUMPRODUCT functions appears redundant. What happens if you remove them from your formula?

Cheers,

**martindwilson** · 11-11-2011, 03:27 PM

@ connexionlost excel 1020 was an abacus!

**Bjordion** · 11-11-2011, 03:29 PM

Hahaha, yes the little known version 1020. Thank you for alerting me to that.

I agree with you on the SUMPRODUCT function, but I already have tried removing it. Doing so causes another #div/0! error.

**ConneXionLost** · 11-11-2011, 03:37 PM

Abacus? - I don't think I have a copy of that version.

In any case, the IFERROR function hides many crimes.

Any chance you could post a sample worksheet?

**Bjordion** · 11-11-2011, 03:53 PM

Thank you for the IFERROR suggestion, if nothing else I will use that.

Unfortunately I would have to heavily, heavily modify the worksheet to remove all confidential information prior to posting, something I don't have time for at the moment.

Thank you very much for your assistance, it is much appreciated.