divide by zero error

[Potsy]

hi

get a #DIV/0! error when divide back by 0 but need to show that if the
value in the invoice column is greater than 0 but quoted value is 0
then it gives 100% hit rate i.e. work was not quoted but an order was
received retrospectively.

Total Quoted = V2
Total Invoiced = W2
Hit Ratio = X2

currently have very basic formula of w2/v2 which works until below
condition is met.

v2 (quoted) is 0 and w2 (inoviced) has a figure and is returning #DIV/
0! error currently.

any help appreciated.

 

[Potsy]

=IF(ISERROR(W2/V2),0,W2/V2)

This will return 0 if there is a divide by zero error

edvwvw

thanks but as well as mask all 0/0 errors i need to show that if there
is a value in invoiced column but not in quotes (i.e. we have gone
straight to order) then it shows all of the value to give a 100% hit
rate otherwise with formula above shows as 0% which I know is
mathmatically correct but not what I want!!!

thanks

 

[Shane Devenshire]

What's with the missing questions?

The basic way to handle divide by zero problems is

=IF(A1=0,"",B1/A1)

 

[Potsy]

What's with the missing questions?

The basic way to handle divide by zero problems is

=IF(A1=0,"",B1/A1)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire



:- Hide quoted text -

- Show quoted text -

sorry, thought i had explained in opening question obviously not very
well! thanks for your help but think the formula will still return a
0, see example below:

Total Quoted = V2
Total Invoiced = W2
Hit Ratio = X2 (expressed as percentage ie W2/V2)

If V2 is NULL and W2 is >0 then I do not want it to return "0" or DIV/
0! error in X2, instead want it to convert 100% hit rate.

hope it makes a bit more sense struggling with this!! thanks

 

[Chris \Smitty\ Smith]

Or test for the denominator:

=IF(A1,B1/A1,"")

Hope that helps,