| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide by Zero Error
=ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Column s,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0)
|
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Divide by Zero Error
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. "hkslater" wrote: =ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Column s,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Divide by Zero Error
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(......)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... =ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Column s,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Divide by Zero Error
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? "T. Valko" wrote: 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(......)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... =ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Column s,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Divide by Zero Error
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,Peri od2,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,Peri od2,3,0)=0),"",ROUNDDOWN((VLOOKUP($D9,Period1,VLOO KUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... 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? "T. Valko" wrote: 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(......)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... =ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Column s,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Divide by Zero Error
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. "T. Valko" wrote: 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,Peri od2,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,Peri od2,3,0)=0),"",ROUNDDOWN((VLOOKUP($D9,Period1,VLOO KUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... 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? "T. Valko" wrote: 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(......)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... =ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Column s,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Divide by Zero Error
Try this:
=ROUNDDOWN(IF(VLOOKUP($D9,Period1,3,0)=0,0,(VLOOKU P($D9,Period1,VLOOKUP(I$6,Columns,3,0),0)/VLOOKUP($D9,Period1,3,0)*$E9))+IF(VLOOKUP($D9,Peri od2,3,0)=0,0,(VLOOKUP($D9,Period2,VLOOKUP(I$6,Colu mns,3,0),0)/VLOOKUP($D9,Period2,3,0)*$F9)),0) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... 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. "T. Valko" wrote: 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,Peri od2,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,Peri od2,3,0)=0),"",ROUNDDOWN((VLOOKUP($D9,Period1,VLOO KUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... 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? "T. Valko" wrote: 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(......)) -- Biff Microsoft Excel MVP "hkslater" wrote in message ... =ROUNDDOWN((VLOOKUP($D9,Period1,VLOOKUP(I$6,Column s,3,FALSE),FALSE)/VLOOKUP($D9,Period1,3,FALSE)*$E9)+(VLOOKUP($D9,Per iod2,VLOOKUP(I$6,Columns,3,FALSE),FALSE)/VLOOKUP($D9,Period2,3,FALSE)*$F9),0) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Divide by Zero Error
Hi,
You can try embedding your formula in an IF function with ISERROR, to return something else instead of the error. =IF(ISERROR(your formula),"something else", your formula)) Dave url:http://www.ureader.com/msg/104234844.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| divide by 0 error jmp | New Users to Excel | |||
| Divide by zero error | Excel Discussion (Misc queries) | |||
| Divide by zero error | Excel Discussion (Misc queries) | |||
| Sum and Divide Formula error | Excel Worksheet Functions | |||
| excel divide by zero error | Excel Worksheet Functions | |||
Hybrid Mode
