Cannot replace #Div/O error

[luckyk]

Dear Members,
I am using a worksheet based on which I need to make a pie chart. There are three main columns Last Serviced Date, Todays Date and Minimum Service Interval. The columns Minimum Service Interval has number 1,2,4 or is blank. The idea is to have these three values is my formula column. Maintained when difference between dates is <365, Not maintained when difference between dates > 365. What I am trying to do is put a formula which would do the following
1.If Difference between Today's Date and Last Service Date > 365, then fill cell with Not Maintained
2.If Difference between Today's Date and Last Service Date >365/(Minimum Service Interval), then fill cell with Not Maintained

This is where the complications start
In the second condition I want to be able to say if Minimum Service Interval is Blank, then use first condition, and fill cell with Not Maintained. Finally if the conditions above are not satisfied then fill cell value as maintained. I have attached an excel sheet to give some clarity

[JBeaucaire]

In D2, then copied down:

=IF(OR(TODAY()-A2>365, B2=0), "Not Maintained", "Maintained")


You don't really need column C.

[luckyk]

Thanks for the quick response.
I tried using then and found a slight problem with the result. My condition 2 is not being satisfied for many values. Example in Row number 77, the last serviced date is 21/06/2013, So difference between today and that cell, should ideally be status Maintained, but with the formula [=IF(OR(TODAY()-A2>365, B2=0), "Not Maintained", "Maintained") ] it displays Not Maintained. Its not always when B2 is blank that it should display "Not maintained". Is there a way to modify this?

[AlKey]

Please try this one

=IF(AND(B2="",TODAY()-A2<365),"MAINTAINED",IF(TODAY()-A2<365,"MAINTAINED","NOT MAINTAINED"))

[luckyk]

Thanks this was useful. But there is a slight query here as well
I was trying to modify the formula to do this =IF(AND(B2="",TODAY()-A2<365),"MAINTAINED",IF(AND(B2>0,TODAY()-A2<365/B2),"MAINTAINED","NOT MAINTAINED")), but it gives me a DIV/0 in few cases where Column B2 does not have a value.
Any thoughts?
The reason being is that the condition for maintained is that the Difference between Today() and Last Service date should be < 365/B2 (or value in Column B)

[AlKey]

You're Welcome. Don't forget to thank those who helped by clicking on Add Reputation *

[luckyk]

Sorry I just edited my previous comment
Need more advise

[AlKey]

Your modified formula gives the same results as the one that I suggested, except it creates an error as a result of the division. What do you want the formula to show instead of error?

[luckyk]

It seems that your modification to the formula gives the same results that mine. What is it that you want those cells to show when error?[/QUOTE]

When I use your formula modified by me i.e [ =IF(AND(B2="",TODAY()-A2<365),"MAINTAINED",IF(AND(B2>0,TODAY()-A2<365/B2),"MAINTAINED","NOT MAINTAINED"))], it throws an error message #DIV/0!. Check rows 58 onwards after you enter the formula mentioned here.


PS: Sorry, for some reason I am not able to click on reply in your previous message.

[luckyk]

In any case if difference between today and last service date is more than 365/ Column B it should display Not Maintained

[AlKey]

Since you using Excel 2003 version this:

=IF(ISERROR(IF(AND(B2="",TODAY()-A2<365),"MAINTAINED",IF(AND(B2>0,TODAY()-A2<365/B2),"MAINTAINED","NOT MAINTAINED"))),"NOT MAINTAINED",IF(AND(B2="",TODAY()-A2<365),"MAINTAINED",IF(AND(B2>0,TODAY()-A2<365/B2),"MAINTAINED","NOT MAINTAINED")))

But please notice that division is not necessary to get the same result.

[luckyk]

I just added this to the previous code. Seems to have worked

=IF(AND(B2="",TODAY()-A2<365),"MAINTAINED",IF(AND(B2="",TODAY()-A2>365),"NOT MAINTAINED",IF(AND(B2>0,TODAY()-A2<365/B2),"MAINTAINED","NOT MAINTAINED")))

[AlKey]

Or even this:

=IF(B2<>"",IF(TODAY()-A2<365/B2,"Maintained","Not Maintained"),IF(TODAY()-A2<365,"Maintained","Not Maintained"))