• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To remove #DIV Error

E

Ecel Dumbo

Member
Hello,


FOr the formula


(VLOOKUP($I1964,$I$27:$V$498,MATCH(J$1957,$I$27:$V$27,0),0)*1000)/(VLOOKUP($I1964,$I$499:$V$969,MATCH(J$1957,$I$27:$V$27,0),0))


I am getting a #DIV error. I tried =IF(ISERROR(VLOOKUP($I1964,$I$27:$V$498,MATCH(J$1957,$I$27:$V$27,0),0)*1000)/(VLOOKUP($I1964,$I$499:$V$969,MATCH(J$1957,$I$27:$V$27,0),0)),"",(VLOOKUP($I1964,$I$27:$V$498,MATCH(J$1957,$I$27:$V$27,0),0)*1000)/(VLOOKUP($I1964,$I$499:$V$969,MATCH(J$1957,$I$27:$V$27,0),0)))


I am still getting the #DIV error. I am using excel 2003. I can see this is a cumbersome formula
 
Hi ,


Since you are getting a #DIV/0! error , the only possibility is if the denominator in the formula is 0 ; hence you should at the following part :


VLOOKUP($I1964,$I$499:$V$969,MATCH(J$1957,$I$27:$V$27,0),0)


What this is supposed to do is :


1. Look for the contents of cell I1964 in the range I499:I969.


2. If the contents are not found , it will return a #N/A error ; since you are not getting this , we can assume that somewhere in I499:I969 , there is a value which matches the value of I1964.


3. Now , the number of columns in the range I499:V969 is 19 ; if the MATCH function returns a value larger than 19 , you will get a #REF! error.


4. If the value returned by the MATCH function is between 1 and 19 , then the VLOOKUP function will return the value within that cell ; if this happens to be 0 , then you will get a #DIV/0! error.


5. You can verify whether you are likely to get zeroes within your range , and if you do get them , what should be the result of the above formula ; otherwise , if you just want to eliminate the error display , you can rewrite your formula as :


=VLOOKUP($I1964,$I$27:$V$498,MATCH(J$1957,$I$27:$V$27,0),0)*1000/IF(ISERROR(VLOOKUP($I1964,$I$499:$V$969,MATCH(J$1957,$I$27:$V$27,0),0)),1,VLOOKUP($I1964,$I$499:$V$969,MATCH(J$1957,$I$27:$V$27,0),0))


What this does is use the denominator if it is non-zero , otherwise use 1 instead. You need to decide whether the overall value returned by the complete formula will be valid in such a case.


Narayan
 
Hi Narayan,


I tried the above formula and I am getting the error message. Could you please have a look at the attached file.


http://speedy.sh/kB4ZD/To-remove-DIV.xls


Thanks
 
Hi ,


Use the following formula in J1941 :


=VLOOKUP($I1941,$I$9:$V$480,MATCH(J$1939,$I$9:$V$9,0),0)*1000/IF(VLOOKUP($I1941,$I$481:$V$951,MATCH(J$1939,$I$9:$V$9,0),0)=0,1,VLOOKUP($I1941,$I$481:$V$951,MATCH(J$1939,$I$9:$V$9,0),0))


Copy this down and across.


Narayan
 
Thank you for your reply Narayan. I tried the above formula. There is an instance where when i divide $780 (product revenue cell# J43) by 0 (quantity cell# J514), i get the answer as $780 instead of blank answer. Kindly check the file below.


http://speedy.sh/z87Jp/To-remove-a-comlplicated-DIV-in-BW-file.xls
 
Hi ,


Change the formula as follows :


=IF(VLOOKUP($I1941,$I$481:$V$951,MATCH(J$1939,$I$9:$V$9,0),0)=0,"",

VLOOKUP($I1941,$I$9:$V$480,MATCH(J$1939,$I$9:$V$9,0),0)*1000/VLOOKUP($I1941,$I$481:$V$951,MATCH(J$1939,$I$9:$V$9,0),0))


Narayan
 
You must log in or register to reply here.
Back
Top