Formula Forensics No. 017 – A Solution to Maljzm’s problem.

Last week at the Chandoo.org Forums Malzjm, asked a question:

“I need your help on calculating the service level if it passed or fail based on a requirement.

So far, I have only managed to create this:

=IF(O:O="","", IF(AND(D:D="SL 3",O:O<=TIME(12,0,0)),"Pass","Fail"))
This works for one argument but if I do nested if, it does not.”

Faseeh, who joined the Forums in January this year, and has been a recent regular responder at the Forums responded with a nice:

=IF((D2)<=(TIMEVALUE(INDEX($C$17:$C$20,MATCH(B2,$B$17:$B$20,0),0))),"PASS","FAIL")

Malzjm, thanked Faseeh for the response and then asked could he explain how the above formula worked.

Being Shy, Not, I chipped in and asked Faseeh would he mind explaining the solution here as a Formula Forensic.

So today we are proud to present Faseeh’s first post at Chandoo.org, a Solution to Maljzm’s problem.

Faseeh’s Formula:

As always at Formula Forensics you can follow along using real data from the example file: Dowload Here

Define the problem:

The formula should take a value from the Severity Column [Column B] and match the Time Resolved [Column D] against the same status value from a standard table. if the actual time is less than standard time then it should display “Pass” otherwise it should show “Fail”.

The formula will take B2’s value (SL1), match it in second table, return the corresponding value from RESOLVED Time (02:00), then compare this value to the value in D2 (1:15) and will return Pass or Fail (Pass in this case)

Formula in E2:

=IF((D2)<=(TIMEVALUE(INDEX($C$17:$C$20,MATCH(B2,$B$17:$B$20,0),0))),"Pass","Fail")

Lets break this formula down and work through it

Step No. 01

=IF((D2)<=(TIMEVALUE(INDEX($C$17:$C$20,MATCH(B2,$B$17:$B$20,0),0))),"Pass","Fail")

The Excel Match() function is used to match a value and give its position in an array.

The Match function takes following arguments,

MATCH(lookup_value,lookup_array,match_type)

For this situation it takes the following this form:

MATCH(B2,$B$17:$B$20,0)

Where:

Lookup_Value = B2

Lookup_array = $B$17:$B$20

Match type: = 0 =  ‘Exact match’

The result will be like this:

MATCH(“SL3”,{”SL1”,”SL2”,”SL3”}, 0) 

Excel will return the value 3

This will be an input for the second function.

Step No. 02

Our formula was…

=IF((D2)<=(TIMEVALUE(INDEX($C$17:$C$20,MATCH(B2,$B$17:$B$20,0),0))),"Pass","Fail")

That take the following shape after evaluation of MATCH() function

=IF((D2)<=(TIMEVALUE(INDEX($C$17:$C$20,3,0))),"Pass","Fail")

So the next Function to be evaluated is INDEX ().

The Excel Index () function a few inputs

Index(Array, Row_num, Column_num)

Note that we are using “Array Form” here [The Index() function could be used in “Reference Form” as well]

For our situation the formula takes this shape:

INDEX($C$17:$C$20 ,3 ,0)

So the

Array: = $C$17:$C$20 is the range containing Resolves Time ,

Row_Num: = 3 the value we get from MATCH()

Column_Num: = 0  stands for we want to look in the Zeroth Column, i.e. the Resolved Time Column in itself so this will give us:

=INDEX ($C$17:$C$20, 3, 0)

=INDEX ({“02:00″,”04:00″,”08:00″,”12:00”} ,3 ,0)

i.e. the Third element of the Zeroth column i.e. “08:00″.

This will be the feed to the next formula.

Step No. 03

After evaluating the INDEX () function our formula takes following shape:

=IF((D2)<=(TIMEVALUE(“08:00”)),”Pass”, “Fail”)

The Excel Timevalue() function returns the decimal number of the time represented by a text string.

The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Thus for the values mentioned in “resolved time” the Timevalue() would give 0.083, 0.167, 0.333, 0.500 respectively.

Hint: How will you get these values manually??… Timevalue = No. of Hours x 60 / Total Min in a Day

so 08:00 Hrs x 60 / 1440 = 0.333

Similarly the others values in the Resolved Time can also be calculated

Note that this functions takes time as ‘text’. Hence by the end of this step the formula stood at:

=IF((D2)<=0.333,”Pass”, “Fail”)

Step No. 04

The final step is an IF() statement that compares value of D2.

Since value in D2 (“1:15” = decimal 0.05208) is less than 0.333, the IF () statement will return “Pass” as the final result.

=IF((0.05208)<=0.333,”Pass”, “Fail”) 

You can check the values below this in E2 in the example file

Download

You can download a copy of the above file and follow along, Download Here.

Formula Forensics “The Series”

Congratulations Faseeh on your first published post at Chandoo.org.

I hope Faseeh is able to continue with similar posts in the future!

You can learn more about how to pull Excel Formulas apart like Faseeh has just shown us in the following posts

Formula Forensic Series

We Need Your Help

I still need more ideas for future Formula Forensics posts and so I need your help!

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post just like Faseeh has done above or;

If you have a formula that you would like explained but don’t want to write a post also send it to Chandoo or Hui.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.