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

Posted on April 5th, 2012 in Formula Forensics , Posts by Faseeh - 10 comments

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.

 

 

Written by Hui...
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

10 Responses to “Formula Forensics No. 017 – A Solution to Maljzm’s problem.”

  1. Luke M says:

    Excellent write-up Faseeh!

  2. Ksandra2901 says:

    That has got to be the best write up of a formula I have seen. Pulled apart, each section clearly explained and the result of each bit of the formula displayed, made this very clear for a "Beginner" like me!

    Good job xx

  3. Chandoo says:

    Very good explanation Faseeh... Thank you so much for writing & welcome.

  4. oldchippy says:

    Nice one Faseeh, keep it going

  5. Faseeh says:

    Dear all! Luke M, Ksandara2901, Chandoo G, & oldchippy!!

    Thanks alot. I am just a humble fan of this site! 😀

  6. Steve says:

    Thanks so much to Chandoo for the website--I have learned so much in a short time. Thanks to Faseeh for the formula and explanation. I have been trying to understand the various look up functions lately so this came at a very good time!

    May I ask if there is a better reason to use the Index andMatch instead of vlookup in this case? Could the formula also be:

    =IF((D2)<=(TIMEVALUE(VLOOKUP(B2,$B$17:$C$20,2,FALSE))),"Pass","Fail")

    Thanks again!

  7. sam says:

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

    • Faseeh says:

      No sam there is none, your formulas are equally correct and even compactor.. I just prefer Index(Match()) over vlookup() because vlookup can't look left to it, it reads only in the first column of the table ..It always go to it right so the first one is a more general one. But in this case you are right.

  8. Melzjm says:

    Wow! This is impressive! It is like looking at an excel debugger! Thank you Faseeh for the helpful explanation. I learned a lot! Please keep up the good work! This formula made me keep my job! 🙂

  9. Udit says:

    Nice way... I just modified it to use vlookup though... I am more comfortable with that than INDEX...

Leave a Reply