2 Dimensional Lookup by column & rows to return score grade
 

I've created a table similar to a mileage chart to score different shop
performance figures

Eg.

*Score*, *Revenue, Sales per opening hr
R, SPOH*,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each
with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on
each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is
greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is
greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I
can't figure out how i'd use that to return a grade A to F?


--
loscherland
------------------------------------------------------------------------
loscherland's Profile: http://www.excelforum.com/member.php...fo&userid=6709
View this thread: http://www.excelforum.com/showthread...hreadid=533663

2 Dimensional Lookup by column & rows to return score grade
 

The easy way would be to invert the table to

000 G 0
400 F 1
500 E 2
600 D 3
etc, and use

=vlookup(A1,D1:E8,2,True)

where A1 is your score, D1 to E8 is the location of your table, and
'true' uses the best found (but lower) value.

Hope this helps

--

which would take the nearest value,

loscherland Wrote:
I've created a table similar to a mileage chart to score different shop
performance figures

Eg.

*Score*, *Revenue, Sales per opening hr
R, SPOH*,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each
with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on
each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is
greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is
greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I
can't figure out how i'd use that to return a grade A to F?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533663

2 Dimensional Lookup by column & rows to return score grade
 

Assuming your table is in cols A to L (cola =Grade, Cols B to L are your
KPIs) as shown below:

R SPOH
G 0 0
F 400 0
E 500 1
D 600 2
C 700 3
B 800 4
A 1000 5

If D10 contains the KPI value, and E10 the KPI heading e.g. SPOH, then try
in say F10:


=INDEX($A$2:$L$8,MATCH(D10,OFFSET(A1,1,MATCH(E10,B 1:L1,0),7,1),1),1)

HTH

"Bryan Hessey" wrote:


The easy way would be to invert the table to

000 G 0
400 F 1
500 E 2
600 D 3
etc, and use

=vlookup(A1,D1:E8,2,True)

where A1 is your score, D1 to E8 is the location of your table, and
'true' uses the best found (but lower) value.

Hope this helps

--

which would take the nearest value,

loscherland Wrote:
I've created a table similar to a mileage chart to score different shop
performance figures

Eg.

*Score*, *Revenue, Sales per opening hr
R, SPOH*,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each
with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on
each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is
greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is
greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I
can't figure out how i'd use that to return a grade A to F?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533663