Can someone fix this?

[LaPeqi]

This website is awesome! Thank you.


Riddle me this (All the info I have found says it should work but excel keeps saying there is an equation error):


=IF(B2>=HOME and C2>=26 and D2>=today()-365,"High Risk",IF(B2>=HOME and C2>=5 and D2>=today()-730,"High Risk",IF(F2>=5,"High Risk",IF(F2>=3,"Medium Risk","Low Risk"))))


We are putting in "HOME" in one column, Number of units in another column, and last date of visit in third - I think the part that is messing it up is the dates.


Here is the rule:


HOME PROPERTY ON-SITE VISIT SCHEDULE

Number of Units Inspection Frequency

1 – 4 Every Three (3) Years

5 – 25 Every Two (2) Years

26 or More Every Year


I also have criteria, and if it makes certain points (over 5=high risk, over 3=med, 1-3=low) - I've gotten this part to work.


Can you master this beast?


THANK YOU!

 

[DaveTurton]

probably your use of "and". To include "and" in your if formula you should write it as below:


if(and(B2>=HOME,C2>=26,D2>=(today()-365),"high Risk,....

 

[DaveTurton]

Also your last High Risk and Medium Risk


High Risk - if F2>=5

Medium Risk if F2 >=3


if F2 was = 6 then it would be both High and Medium risk


You probably want if(and(F2>=3,F2<5),"Medium Risk",if(F2>=5,"High Risk")

 

[DaveTurton]

You could also make the formula tidier by using the OR function

 

[Faseeh]

Can you post some sample data? What is present in Column named Home?


Faseeh

 

[NARAYANK991]

Hi ,


The problem is the check for the date :


As I understand it , the risk is based on whether the number of days , since the last inspection , has exceeded the permissible limit.


So if the date of the last inspection is in D2 ; we should check whether the number of days between that date and today is more than 365 , 730 or 1095.


The correct way to formulate this requirement is :


TODAY() - D2 >= 365 or TODAY() - D2 >= 730 or TODAY() - D2 >= 1095


Your phrasing :


D2>=today()-365


is equivalent to :


TODAY() - D2 <= 365


which is not what you want , I think.


Narayan