SUMPRODUCT and #DIV/0! Error

[TomYC]

Is there a way to work around existing #DIV/0! errors that I want to "query" with a SUMPRODUCT function?

 

[SkipVought]

Use the IF statement to test the value...
[tt]
=IF(SomeOtherValue=0,0,SomeValue/SomeOtherValue)
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Thanks, Skip--
If I understand you correctly, you suggest testing for the value that produces the divisor, hence zero leads to #DIV/0! error. In my case, that error is on a different worksheet ('4 Monti' in my example below) and it is only an error because a formula has not yet received data. So I don't what to mess with some zero divisor value on some third worksheet (if I don't have to!), but rather I was hoping to test for the error with something like IF(ISERROR( and wondered if I could do all of that inside the far term of a SUMPRODUCT such as this:
=SUMPRODUCT(('4 Monti'!$A$4:$A$368>DATEVALUE("3/31/2010"))*('4 Monti'!$A$4:$A$368<DATEVALUE("5/1/2010"))*(('4 Monti'!$C$4:$C$368)))

 

[SkipVought]

because a formula has not yet received data.

Not a very good practice to pre-enter formulas for not yet arrived at data. You are experiencing one of the pitfalls of doing so.

The Excel feature Data > List > Create list precludes having to do that, as each formula is automaically added when you add new lines of data to the table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

or SomeValue/(SomeOtherValue+0.000000000001)

Gets rid of the error and (arguably) is less misleading than a 0

OR, more sophisticated:
This gives 99999% when a budget figure of zero is used, but gives zero when both budget (D43) and actual (E43)are zero.
=IF(D43=0,IF(E43=0,0,999.99),(E43-D43)/ABS(D43))




Gavin

 

[TomYC]

I hear you both!
When I wasn't in a situation where I needed SUMPRODUCT, then SUMIF could easily work around the #DIV/0! errors. The problem with "fixing" the latter, as I see it, is that it involves going into hundreds or thousands of cells with formulae such as
A1=B1/C1 and changing each one to =IF(C1=0,0,B1/C1) which I have not figured out how to automate over a large selection of cells!
But what I'm gathering from the discussion is that SUMPRODUCT can't be engineered to skip over ISERROR types.

 

[SkipVought]

involves going into hundreds or thousands of cells with formulae such as

HUH???

Ever hear of copy 'n' paste?

But since you problem in is a SOURCE TABLE, simple fix it at the source! I gave you a very simple solution!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Here's an example of a cell formula that produces the div/0 error:
=VLOOKUP($A149,'Q:\StaffingEffect\PCH Spreadsheets\2010\[PCH spreadsheet CARDIAC 052910.xls]4M'!$A$3:$AL$19,7,FALSE)/B149
IF there is a global (e.g. copy/paste) method of converting this to:
=IF(ISERROR(=VLOOKUP($A149,'Q:\StaffingEffect\PCH Spreadsheets\2010\[PCH spreadsheet CARDIAC 052910.xls]4M'!$A$3:$AL$19,7,FALSE)/B149),0,=VLOOKUP($A149,'Q:\StaffingEffect\PCH Spreadsheets\2010\[PCH spreadsheet CARDIAC 052910.xls]4M'!$A$3:$AL$19,7,FALSE)/B149) Then I'm all for it!
Or even a way to set the value to zero if the divisor is zero, in this case, B149. But each cell is going to be different, no?

 

[Gavona]

If I copy your formula down I get:
=VLOOKUP($A150,'Q:\StaffingEffect\PCH Spreadsheets\2010\[PCH spreadsheet CARDIAC 052910.xls]4M'!$A$3:$AL$19,7,FALSE)/B150

Is that your current formula?

If so then:
=if(B150=0,0,VLOOKUP($A150,'Q:\StaffingEffect\PCH Spreadsheets\2010\[PCH spreadsheet CARDIAC 052910.xls]4M'!$A$3:$AL$19,7,FALSE)/B150)
Would replace it and could be copied up/down.

Gavin

 

[SkipVought]

Is not the B149, the value in the denominator???

Why are you testing the lookup?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

So true, Gavin--
But I have even more cells that are like this:
=((VLOOKUP($A4,'Q:\StaffingEffect\PCH Spreadsheets\2010\[PCH spreadsheet CARDIAC 010910.xls]4M'!$A$3:$AL$19,7,FALSE))*7.5)/VLOOKUP($A4,'Q:\StaffingEffect\PCH Spreadsheets\2010\[PCH spreadsheet CARDIAC 010910.xls]4M'!$A$3:$AL$19,6,FALSE)
where the divisor is itself a VLOOKUP value.
But your point is well taken!

 

[TomYC]

I didn't mean for this to get so drawn out!
I have a potential for a zero in a source file (above, in the eample, the source file would be [PCH spreadsheet CARDIAC 010910.xls]4M. A second file performs a VLOOKUP on this file, as part of a calculation in which the zero in the source file ends up as the denominator.
My SUMPRODUCT is or was addressing this second file, and I had asked about how to step around the div/0 error that the second source file may display as a consequence of a value in the first, original source file. If I read Skip correctly, he is remarking that I should design the second source file such that it cannot generate errors of the div/0 variety, using a method such as what Gavin suggests. All true! But given the variety of syntax that I have to work with, I don't see a global solution to this yet.
T

 

[Gavona]

I am a little confused about your data stucture.
Go back to Skip's post of 15:04 - any prospect?

Or maybe you need to:
make a copy of your '4 Monti' worksheet,
Copy to values,
Edit,Replace to get rid of the errors,
Reference the new (temporary) sheet instead

To refresh just copy from '4 Monti',
pastespecial,values to the values one
Edit, replace

Gavin

 

[TomYC]

Thanks, Skip and Gavona--
I'll puzzle over this some more next week.
Some of your suggestions work for me, while others do not, primarily as I am using Excel as a kind of database here, for other users to make entries in various spreadsheets. I am working on what I call and aggregator spreadsheet, to pull all the data together, whenever it appears (new lines are entered daily by many users). So once all the data for the month is in, there should be no "zeroes" to cause a fuss.

 

[SkipVought]

There should NEVER be unwanted data to contend with in a well designed workbook! You need to revisit your workbook design.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}