Apr 25 2019 09:38 AM
Greetings, all. I've been tasked with creating a live service interval tracking formula for our onsite equipment. I have current hours, and service intervals are 250 hrs and 500 hrs respectively.
=IF(AND((E30-C30)<500),"OK","SERVICE")
C30 = last serviced hrs
E30 = current live hrs
I'm wondering if there is a better formula I can use? I'm a caveman when it comes to Excel, so this is the best I've come up with :(.
I'd like to have OK in green and SERVICE in red.
Any suggestions would be greatly appreciated. Thank you for your time.
BP
Apr 25 2019 10:25 AM
@_BP_ ,
You don't need AND here, just
=IF((E30-C30<500),"OK","SERVICE")
and you may use conditional formatting to color your cells automatically depends on hours difference.
Apr 25 2019 11:10 AM
@Sergei Baklan thank you for your prompt aide. Can you tell me what formula I would use to colour the column when Service is the result of the first formula? I am unclear where to even place the formula, nevermind how to scribe it.
BP
Apr 25 2019 11:31 AM
@_BP_ , please check this post https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/comment... how to work with conditional formatting.
Your formula could be like
=($E1-$C1)<500
for the one color, and similar one for another. Two colors - two rules.
If you provide small sample file I could illustrate details on it.
Apr 25 2019 11:59 AM
Sounds good, @Sergei Baklan. Truly learning as I go. Thanks for the link. I will peruse it thoroughly. I'm looking to create a parameter that includes a 50 hr warning color prior to service being due as well. lol Big dreams. Green = OK, yellow = 50 hrs out from next service, red = SERVICE. I update the hrs weekly.
Apr 25 2019 03:48 PM
@_BP_ ,
First, better to define parameters of your formulas in the cells and not to hardcode in formulas. Plus give them some names to make formulas more understandable and friendly. Even better to keep them in separate from report sheet. For now let take in the same sheet as it was like this
To give the name stay on the cell, type in left upper box desired name, Enter.
Next select entire column D, Home->Conditional Formatting->New Rule and here select
Select proper rule, "equal to" from drop-down menu, next equal to what (don't forget =), select desired format and Ok to close all these windows.
Repeat the same for the ServiceLevel1.
Next, better to test the formulas for conditional formatting rules in separate columns. If the formula returns TRUE it will trigger the formatting, otherwise it'll be ignored. Start from the second row, be sure it works correctly for your rows with data. Copy it into te first row, it returns error but that doesn't matter in our case. Error is also result.
Now select entire column F, new rule using formula, copy formula from cell M1 (we have to select formula from first row from which our range starts) and paste it here. Apply format for the red and Ok.
Repeat the same for the alert (yellow rule), and put it on the second place. Next Green and move it on the third place.
Formulas are moved up and down by these arrows
Sequence is important for thirst two rules, otherwise we have to complicated formulas (first we check if hours > limit, after that if > limit-gap. With such sequence it works, otherwise we shall check both boundaries for yellow rule).
In addition, for the green rule we check if Current Hours are not empty not to color blan cells.
=(($E1-$C1)<=IF($D1=ServiceLevel2,HoursSL2,HoursSL1)-HoursGap)*($E1>0)
Forgot to say, I modified yout formula in column F as well
=IF((E2-C2<IF($D2=ServiceLevel1,HoursSL1,HoursSL2)),"OK","SERVICE")
Apr 26 2019 11:55 AM
Indubitably, @Sergei Baklan, the best direction given to date for my exploits within Excel. Thank you for the link and the walk through of creating the needed formulas. I still have lots of reading and practicing to do, so I'll carry on. Have a good weekend.
Thx again.
BP
Apr 26 2019 01:44 PM
@_BP_ , you are welcome. Exploring Excel is never ended process, good luck here.