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

 

[jalenben1]

Skip:

I like what you have
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

But is there any way to change Exceeds to Missed and where the hhours are listed to say Met?

When I tried that formula in my spreadsheet I have the hours on the same spreadsheet that I hide in column AK as below
Coulmn
AK 2 0:00
AK 3 4:00
AK 4 8:00
AK 5 12:00
AK 6 18:00
AK 7 Exceeds

 

[SkipVought]

You can use a simple IF statement.

Skip,

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

 

[jalenben1]

So how would you suggest I write the IF statement?

 

[SkipVought]

Look, you're a programmer. Use the Excel HELP for IF. Give it a try. Post back with what you tried and what the results were if you run into problems.

Skip,

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

 

[jalenben1]

Skip:

soo farI am using this formula:

=IF(TimRanges,MATCH(AK1:AK6,TimRanges,1)+1,1) but I am getting an error that says #NAME?

what am I doing wrong?

 

[SkipVought]

What happend when you use this...
[tt]
=INDEX(TimRanges,MATCH(A2,TimRanges,1)+1,1)
[/tt]
in row 2?

Skip,

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

 

[jalenben1]

I got #Name? as an error

 

[SkipVought]

Did you Name the range using Insert > Name > Create -- Create Name in TOP row?

You must SELECT the entire TimRanges range, heading and all, before doing that process.

Skip,

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

 

[jalenben1]

If I go to Sheet 1 in my spreadsheet and use the formula

=INDEX(TimRanges,MATCH(C2,TimRanges,1)+1,1)

based of the following:

The Time Range Standard table

Cloumn C
TimRanges
0:00
4:00
8:00
12:00
18:00
Exceeds

The formula...

=INDEX(TimRanges,MATCH(C2,TimRanges,1)+1,1)

Will that get me my desried results below

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

 

[SkipVought]

If your actual durations are in column [red]A[/red], starting in row [red]2[/red], then
[tt]
=INDEX(TimRanges,MATCH([red]A2[/red],TimRanges,1)+1,1)
[/tt]

Skip,

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

 

[jalenben1]

In this case the actual durations are in the same spreadsheet with the data in column X, starting in row 2, but the criteria

TimRanges
0:00
4:00
8:00
12:00
18:00

are on another worksheet (Sheet 1) in column C, row 2

 

[SkipVought]

It does not matter where the TimRanges lookup table is, as long as you have NAMED the RANGE in the lookup table.

You can be on ANY sheet in your workbook and look in the Name Dropdown just above the top-left cell displayed in your sheet, and SEE, TimRanges. If your SELECT that Name, it will ACTIVATE the sheet and SELECT the data range.

If you do NOT see TimRanges in the Name Box, you do not have the data range named.

Skip,

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

 

[jalenben1]

This is what I have and still noo luck

=IF(TimRanges,MATCH($X2,Sheet1!$C$2:$C$7,1)+1,1)

 

[SkipVought]

If X2 contains a duration like 19:15 then
[tt]
=INDEX(TimRanges,MATCH($X2,[red]TimRanges[/red],1),1)+1,1)
[/tt]
returns Exceeds


Skip,

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

 

[jalenben1]

Skip:

I am using the formula you provided and it still says #Name. I dont see EXCEEDS for the duration 19:15

Do I need to put the criteria

TimRanges
0:00
4:00
8:00
12:00
18:00
Exceeds

on the same worksheet as the formula?

 

[SkipVought]

...it still says #Name...

because you have NOT NAMED the TimRange range!

Look up in Excel HELP

Define named cell references or ranges

particularly Convert existing row and column labels to names

Skip,

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

 

[jalenben1]

OK..I think I get it..I put in the ranges of the duration..so now I have

=INDEX(TimRanges,MATCH($X2,$C$2:$C$7,1),1)+1,1)

Is this correct??

 

[SkipVought]

Absolutely [red]NOT[/red]!
[tt]
=INDEX(TimRanges,MATCH($X2,[red]TimRanges[/red],1),1)+1,1)
[/tt]
Here's what each function means.
[tt]
MATCH($X2,TimRanges,1)
[/tt]
Find where the value in range X2, appears in the range TimRanges, if it's less than or equal to a value in TimRanges.

So if the value is 3:30, that is greater than the FIRST value in TimRanges, 0:00, but LESS THAN 4:00. So the MATCH function returns 2.
[tt]
from the [red]MATCH[/red] function
|
v
=INDEX(TimRanges,[red]2[/red],1)
[/tt]
mean from the TimRanges range return the [red]SECOND [/red] value in the FIRST column.

You can refer to Excel HELP on INDEX and MATCH, 2 very useful functions that EVERY SERIOUS EXCEL USER ought to be familiar with and use.

Skip,

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