• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula on calculating Service Level Performance

M

melzjm

New Member
Hi,


It's me again. 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. The file is below.


https://docs.google.com/open?id=0B8ipP_XUVnFwSk5Ec1l2Y0ZTbnVLYTQzZUZ3bEtfUQ


Any assistance would really be appreciated.


Thanks,

Melanie
 
Hi melzjm,


Enter this formula in E2 & drag down:


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


PS: There is some problem with Column B of your sheet, edit cells for any extra spaces after SL~ because initially they were giving N/A Error and after i removed extra spaces after them it did worked. Secondly I have changed the resolved time to this format removing sign of 'less then equal to" and set it as 'Text'. See this: 

[pre]RESOLVED Time
SL1	02:00
SL2	04:00
SL3	08:00
SL4	12:00
[/pre]

Its working on my excel sheet, hope it helps!


Regards,

Faseeh
 
Hi Faseeh,


I entered the formula in E2 , dragged it down but I get this result . does it have to be formatted in a different way? thanks for ur help/


#N/A

#N/A

#VALUE!

#N/A

#N/A

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!
 
Hi Faseeh!!


Wow! That was amazing! You are amazing! It worked after I read all the changes u made! Thank you. For my learning, can u help me explain how the formula worked? I would like to understand it. Id do not know how the index work and the time value. Why did the space in my column B, affected the result?

- if the resolved time was in text, how did it work?


Looking for your help.


Thanks.
 
Hi melzjm,


The sheet is still open in front of me and it is working!! Have you deleted the extra spaces in columns B (press F2 to see any extra spaces in there) and formatted the resolved time as i mentioned to you?? Other wise give me your email address!


Faseeh
 
Well It worked for then its really good, I will revert and explain it (Its office time actually) Stay in touch!!


Glad to Help!

Faseeh
 
Faseeh

I'd like to use this as a Formula Forensic

Would you like to write it up and we can post it this Thursday?


I can do it if you don't have time


Hui...
 
Hi Hui,


I will (hopefully) do it by tomorrow eve and will mail you so that if there is a mistake you can correct that.


Thank You,

Faseeh
 
@All

Please note Faseeh's response to this question here

http://chandoo.org/wp/2012/04/05/formula-forensics-no-017/


Congratulations also on Faseeh's First Chandoo.org Post
 
You must log in or register to reply here.
Back
Top