Need help with an if=then formula

[lathode]

edit: My mistake, if b equals less than a it will display 0%. Otherwise having N/A display instead of #DIV/0 would be nice, it's not real necessary.





I am trying to make a formula for the followig question:

a= Number of deliveries
b= delivered on time
c= called with an updated arrival time (asked only if customer answers no to b)

Originally I used =c/(a-b) However the problem with that is if everyone was delivered on time then I have "blank" for c which gives me #DIV/0! Or if the answer is 0 I also get #DIV/0!

Can I have a forumla so that if c="blank" then display N/A, if c=0 then display "0%", if there's any other number then =c/(a-b) ?

Thank you for your help.

[Paul]

Hi Lathode, welcome to the forum.

Perhaps try:

=IF(AND(ISNUMBER(C1),C1=0),0,IF(C1="",NA(),C1/(A1-B1)))

Note, if you want to return the text "N/A" instead of the error #N/A, replace NA() with "N/A".

[lathode]

Thank you for your help Paul, this looks like a great site. Glad I found it through Google.

[Pete_UK]

Try this (and relate it to how you have asked the question):

=IF(c="","n/a",IF(c=0,0,IF(a=b,0,c/(a-b))))

Format the cell as percentage (and unless a, b and c are named values, insert the appropriate cell references for them).

Hope this helps - if so, mark the thread as Solved (the FAQ tells you how).

Pete