Nested IF AND OR Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2439834%22%20slang%3D%22en-US%22%3ENested%20IF%20AND%20OR%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439834%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20fairly%20simple%20project%20but%20have%20hit%20a%20roadblock%20in%20my%20Excel%20knowledge.%20I%20want%20to%20track%20oil%20change%20and%20tire%20rotation%20for%20a%20fleet%20of%20vehicle.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20record%20mileage%20at%20last%20oil%20change%2C%20tire%20rotation%20and%20current%20mileage.%20I%20want%20to%20be%20able%20to%20quickly%20see%20which%20vehicle%20needs%20service%20based%20on%20two%20triggers%2C%205000%20%26gt%3B%3D%20oil%20and%2010%2C000%20%26gt%3B%3D%20tires.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20Current%20Miles%20-%20Miles%20Last%20Oil%20Change%20%26gt%3B%3D500%20AND%20Current%20Miles%20-%20Miles%20Last%20Rotation%20%26lt%3B%3D10%2C000%2C%20OIL%20ONLY%2C%20NO%20SERVICE%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3EIF%20Current%20Miles%20-%20Miles%20Last%20Oil%20Change%20%26gt%3B%3D4500%20AND%20Current%20Miles%20-%20Miles%20Last%20Rotation%20%26lt%3B%3D10000%2C%20OIL%20SOON%2C%20NO%20SERVICE%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3EIF%20Current%20Miles%20-%20Miles%20Last%20Oil%20Change%20%26lt%3B%3D5000%20AND%20Current%20Miles%20-%20Miles%20Last%20Rotation%20%26gt%3B%3D9500%2C%20ROTATE%20SOON%2C%20NO%20SERVICE%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3EIF%20Current%20Miles%20-%20Miles%20Last%20Oil%20Change%20%26lt%3B%3D5000%20AND%20Current%20Miles%20-%20Miles%20Last%20Rotation%20%26gt%3B%3D10%2C000%2C%20ROTATE%20ONLY%2C%20NO%20SERVICE%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3EIF%20Current%20Miles%20-%20Miles%20Last%20Oil%20Change%20%26gt%3B%3D5000%20AND%20Current%20Miles%20-%20Miles%20Last%20Rotation%20%26gt%3B%3D10%2C000%2C%20OIL%20%26amp%3B%20ROTATE%2C%20NO%20SERVICE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20explains%20what%20I%20am%20trying%20to%20do.%20I%20want%20a%20%22flag%22%20when%20oil%20%26amp%3B%20tires%20are%20within%20500%20miles%20of%20their%20due%20date%2C%20a%20%22do%20now%22%20when%20it's%20either%20just%20oil%20or%20just%20rotate%20and%20a%20%22do%20now%22%20when%20it%20is%20both.%20I%20have%20tried%20nesting%20the%20IF%2C%20AND%2C%20OR%20statements%20and%20have%20ended%20up%20with%20a%20hot%20mess.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20very%20much%20for%20any%20help%20you%20can%20provide!%20Linda%20Williams%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2439834%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439973%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20AND%20OR%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076837%22%20target%3D%22_blank%22%3E%40lswobx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%20using%20a%20lookup%20table%3F%20See%20the%20attached%20version.%20You%20can%20change%20the%20text%20in%20the%20lookup%20table%2C%20of%20course.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440084%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20AND%20OR%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076837%22%20target%3D%22_blank%22%3E%40lswobx%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20didn't%20really%20say%20anything%20about%20how%20your%20current%20data%20is%20laid%20out.%20But%2C%20if%20it%20were%20me%2C%20I%20would%20set%20up%20separate%20fields%20for%20the%20oil%20and%20tire%20service%20to%20try%20to%20make%20it%20easier%20to%20work%20with%20and%20filter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20an%20example%20that%20I%20hope%20will%20be%20helpful.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am working on a fairly simple project but have hit a roadblock in my Excel knowledge. I want to track oil change and tire rotation for a fleet of vehicle. 

 

I record mileage at last oil change, tire rotation and current mileage. I want to be able to quickly see which vehicle needs service based on two triggers, 5000 >= oil and 10,000 >= tires. 

 

IF Current Miles - Miles Last Oil Change >=500 AND Current Miles - Miles Last Rotation <=10,000, OIL ONLY, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change >=4500 AND Current Miles - Miles Last Rotation <=10000, OIL SOON, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change <=5000 AND Current Miles - Miles Last Rotation >=9500, ROTATE SOON, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change <=5000 AND Current Miles - Miles Last Rotation >=10,000, ROTATE ONLY, NO SERVICE

OR

IF Current Miles - Miles Last Oil Change >=5000 AND Current Miles - Miles Last Rotation >=10,000, OIL & ROTATE, NO SERVICE

 

I think this explains what I am trying to do. I want a "flag" when oil & tires are within 500 miles of their due date, a "do now" when it's either just oil or just rotate and a "do now" when it is both. I have tried nesting the IF, AND, OR statements and have ended up with a hot mess.

 

Thanks so very much for any help you can provide! Linda Williams

2 Replies

@lswobx 

How about using a lookup table? See the attached version. You can change the text in the lookup table, of course.

@lswobx

 

You didn't really say anything about how your current data is laid out. But, if it were me, I would set up separate fields for the oil and tire service to try to make it easier to work with and filter.

 

Attached is an example that I hope will be helpful.