Formula 1

[jalenben1]

COLUMN A COLUMN B COLUMN C
Priority MTTR Response Time KPI (Met/Missed)
Critical 0:04
Major 0:56
Minor 0:53
Critical 0:32


I am trying to write a formula that if, for example, Column A is equal to Critical and if Column B is <= 15 min then KPI is Met.

I need a little help with configuring the KPI's

For Critical = 15mins
Major = 15mins
Minor = 35mins

I hope this was explained clear enough

 

[dallen43]

I created a named range "cond_tbl" with the following information:

Critical 0:15
Major 0:15
Minor 0:35

Then I used the following formula:

=IF(B2<=VLOOKUP(A2,Cond_tbl,2,FALSE),"Met","Missed")

It seemed to work for me.

 

[jalenben1]

I am a little confused....you put

Critical 0:15
Major 0:15
Minor 0:35

in the same column then called it Cond_tbl?

 

[dallen43]

jalenben1

On a separate worksheet, I entered the 3 conditions "Critical", "Major" and "Minor" in cells A1, A2 and A3. Then entered the times 0:15, 0:15 and 0:15 in cell B1, B2 and B3.

I then selected all 6 cells (A1:B3) and did a Insert->Name->Define and called it "Cond_tbl".

In the formula, you could replace "Cond_tbl" with "Sheet1!$A$1:$B$3".

Sorry for any confusion,
Deb

 

[jalenben1]

Deb:

I think I am almost there...Look below at what I have to get a better picture

Priority MTTR Response Time KPI (Met/Missed)
Major 0:07
Minor 0:42
Minor 0:45
Major 0:22
Major 0:16
Major 0:12
Major 0:12
Major 9:37

If the data is in real time as you see it and my criteria for the KPI's are below

Critical 0:15
Major 0:15
Minor 0:35

I am not sure how I would know if the KPI is met or missed Could you further explain ples? Sorry if I was not clear enough as well.

 

[dallen43]

jalenben1,

Sorry if I wasn't clear.

In a workbook, set up two worksheets:

sheet1: contains the criteria in cells A1 through B3.

sheet2: contains the data. Row 1 contains the headers. Cells A2 through B9 contain the priority and Response Times.

Then in cell C2, place the following formula:
=IF($B2<=VLOOKUP($A2,Sheet1!$A$1:$B$3,2,FALSE),"Met","Missed")

Then copy the formula down through C9.

Hopefully this is clear enough.
Deb

 

[jalenben1]

Deb:

Thanks it worked beautifully. I reaslly apreciate it. Sorry if I was not clear before.

Dwayne

 

[jalenben1]

Deb:
I have another questions. I need a formula much like the same one you helped me with me but this time I am measuring against hours

4 hours
8 hours
12 hours
18 hours

I want to see if the MTTR was Met or Missed. Can u help me with the formula?

 

[jalenben1]

Maybe I need to be a little bit more clear. I have the following:

Column A KPI(Met/Missed)
19:15
0:42
11:31
10:06
0:06
21:23
2:48
0:02


I am measuring against the following below:

4 hours
8 hours
12 hours
18 hours

 

[dallen43]

jalenben1,

In your condition, what times indicate met or missed?

In your data, is there just times? Or is there additional criteria?

Can you please explain what you are trying to accomplish?

Thanks,
Deb

 

[jalenben1]

With the other formula you helped me with it worked fine..which was measuring KPI Met/Missed by the criteria Critical, Major and Minor. Now I am measuring MTTR (Mean Time to Restore but only by the following criteria:

4 hours
8 hours
12 hours
18 hours

So if
olumn A KPI(Met/Missed)
19:15
0:42
11:31
10:06
0:06
21:23
2:48
0:02

What formula would I use to measure the KPI?
Those hours are not followed with a criteria as the other measure. The restoral KPI meausre are just with the hours above.

Remember with the Response time with the criterias Critical, major and minor that formual worked just fine as you provided. I just need to measure the Restoral time. I hope this was clear enough for you. Thanks

 

[SkipVought]

Hi,

1. Construct a lookup table of time range standards...
[tt]
TimRanges
0:00
4:00
8:00
12:00
18:00
[/tt]
2. Name the range using Insert > Name > Create -- Create name in top row.

3. the formula
[tt]
=INDEX(TimRanges,MATCH(A2,TimRanges,1),1)
[/tt]
assuming that your time value is in A2

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jalenben1]

How do I tell if the KPI is met or Missed?

 

[SkipVought]

Now I am measuring MTTR (Mean Time to Restore but only by the following criteria:
[tt]
4 hours
8 hours
12 hours
18 hours[/tt]

You throw something out there, without explaning what your want.

So please explain what this means with respect to what you need.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jalenben1]

OK..Here goes. I am trying to measure the KPI of Response time and Restoral time. For the response time I had the following:

Priority MTTR Response Time KPI (Met/Missed)
Major 0:07
Minor 0:42
Minor 0:45
Major 0:22
Major 0:16
Major 0:12
Major 0:12
Major 9:37

If the data is in real time as you see it and my criteria for the KPI's are below

Critical 0:15
Major 0:15
Minor 0:35

Deb assisted me with the writing of the formula in which I have =IF($V2<=VLOOKUP($H2,'Huawei Tickets Report'!$AI$1:$AJ$3,2,FALSE),"Met","Missed"). That formual worked perfectly and it captured which trouble ticket that were Met or Missed.

For the Restoral Time in which I am wanting to measure the KPI of the ticket that meet the measurable time of the following:
4 hours
8 hours
12 hours
18 hours

In the case I do not have a criteria for the Restoral time as I had for the response time. So now I need second formula for the restoral time based off of the hours above. Does thatmake more sense?

 

[SkipVought]

No, because you STILL have not stated what RESULT you expect. How does 4 8 12 18 relate to your actual data. I gave you a formula that returns 4 8 12 or 18. WHAT DO YOU WANT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jalenben1]

The formula you gave me earlier is not working because it is not giving me the end results and that is if a ticket is Met or Missed based off of the hours given from the Restoral time given.

For example if in my column I have the Restoral time calculated; based of the set hours of 4, 8 , 12 and 18 hours then I need to know in a formula if a ticket is met or missed. You gave this formula:

=INDEX(TimRanges,MATCH(A2,TimRanges,1),1)

it is not telling me if a ticket is met or missed.

 

[SkipVought]

Met or Missed WHAT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jalenben1]

When I say Missed or Met I am referring to if the ticket was restored in the allotted hours of 4, 8, 12 or 18 hours. If I have the following

Column A KPI Restoral Time(Met/Missed)
19:15
0:42
11:31
10:06
0:06
21:23
2:48
0:02

then I need a formula to tell me if the ticket was restored in the allotted hours 4, 8, 12, or 18. If it did then the KPI was met; if it did not then the KPI is missed.

 

[SkipVought]

Is this what you mean?

The Time Range Standard table
[tt]
TimRanges
0:00
4:00
8:00
12:00
18:00
Exceeds
[/tt]
The formula...
[tt]
=INDEX(TimRanges,MATCH(A2,TimRanges,1)+1,1)
[/tt]
My Results
[tt]
Column A KPI Restoral Time(Met/Missed)
19:15 Exceeds
0:42 4:00
11:31 12:00
10:06 12:00
0:06 4:00
21:23 Exceeds
2:48 4:00
0:02 4:00
[/tt]
BTW, you might be able to use this TIP and create a formula that does what you want. So when you post back, tell us what you have tried and what the results are that are not correct.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}