Divide by Zero Error

  • Thread starter Thread starter hkslater
  • Start date Start date
H

hkslater

=ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Period2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0)
 
Sorry I accidentally hit the post button before I completed my question. I
am having a problem with the formula below when there is a zero in one of the
lookup cells in the named region Period1 and Period2. I am not sure how to
modify the formula so that when those cells are zero or blank it doesn't
return an error message.
 
Your #DIV/0! error could come from either:

VLOOKUP($D9,Period1,3,FALSE)*$E9
VLOOKUP($D9,Period2,3,FALSE)*$F9

The VLOOKUP portion could be OK but if either E9:F9 are empty/0 that will
also cause the error.

I'd use a helper cell with this formula assuming that the lookup values are
always present and you don't get #N/A errors:

=IF(OR(VLOOKUP($D9,Period1,3,0)*$E9=0,VLOOKUP($D9,Period2,3,0)*$F9=0),0,"OK")

Then test that helper cell for 0:

=IF(A1=0,"",ROUNDDOWN(......))
 
It is the lookup values in Period1 and Period2 that are occasionally blank or
zero. E9 and F9 always have a value. How would you address that?
 
I would use a helper cell as I suggested in my other reply. Just remove the
references to E9:F9 -

A1 = helper cell formula:

=IF(OR(VLOOKUP($D9,Period1,3,0)=0,VLOOKUP($D9,Period2,3,0)=0),0,"OK")

Then:

=IF(A1=0,"",ROUNDDOWN(......))

Or, you could add the formula above to the front of your current formula but
look how long and ugly it gets:

=IF(OR(VLOOKUP($D9,Period1,3,0)=0,VLOOKUP($D9,Period2,3,0)=0),"",ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Period2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0))
 
Using the helper cell formula results in a blank cell if either Period1 or
Period2 is zero or blank. However, if Period1 is blank but Period2 is not I
need to get the results of the second half of the equation (eg. Period1
equals = 0 + results of Period2 equation) or vice versa if Period1 has a
result but Period2 is blank.
 
Try this:

=ROUNDDOWN(IF(VLOOKUP($D9,Period1,3,0)=0,0,(VLOOKUP($D9,Period1,VLOOKUP(I$6,Columns,3,0),0)/VLOOKUP($D9,Period1,3,0)*$E9))+IF(VLOOKUP($D9,Period2,3,0)=0,0,(VLOOKUP($D9,Period2,VLOOKUP(I$6,Columns,3,0),0)/VLOOKUP($D9,Period2,3,0)*$F9)),0)
 
Back
Top