Calculating performance/potential in 9 Box uisng excel

Hi everyone,

First time adding a thread so please forgive me if i miss out some details.

The company I work for are reviewing their professional development review forms to include a 9 box matrix. They have designed the initial version in word however I feel it would be much better as an excel spreadsheet. Therefore I have having a play around with trying to make it work. However, Im struggling to get it to do what i want. Please see the synopsis of what I would like it the sheet to do.

1) On the 'Potential' tab users have to grade themselves on a series of questions. The sheet works out there 'potential' percentage score in cell C53.
2) On the 'Performance' tab users have to write down the goals for upcoming year and rate their achievement out of 3. They can have a maximum of 4 targets for the year. This sheet works out the percentage performance score in L30.
3) Now for the tricky bit - using these 2 percentages I now need to calculate the individuals 9 Box matrix score on tab 3. I have tried Nested Ifs and Vlookups but am having problems.

I have attached an example to this thread for you to see. PDR 9 Box spreadsheet.xlsx

If anyone could suggest a way for me to be able to highlight which performance/potential combination on the 9 box matrix the individual is on tab 3 in Cell D2, that would be much appreciated. If it could automatically indicate this aswell in the table (perhaps with conditional formatting) that would be the icing on the cake.

Any help much appreciated,

Thankyou

Laurence

Hi and welcome

My first question would be what are the percentage thresholds that decide which are low/moderate/high?
Once you have those, placing someone in the 9 box grid should be a walk in the park.

BSB

Hi and thanks for the reply/help.

I really should have have put that information in the post sorry!! thresholds are as follows -

High is 66 - 100
Moderate is 33 - 65
Low is 0 - 32

Hope that helps

Many thanks

Laurence

One simple method using a lookup matrix. See the amber coloured cells I've added to the '9 Box Matrix' sheet in the attached version of your file.

Essentially it just works out low/mod/high based on the two percentages, then an INDEX/MATCH/MATCH formula in the yellow cell at the top will return the relevant result.

Any use?

BSB

Hi BSB,

That works brilliantly. Over 250 people will need to complete this form so I will just have to make sure every enables the content before filling it out. If they forget to enable im presuming it wont work? Could this be a problem?

Is there a way to highlight the specific cell on the 9 box grid that they fall into? Is this as simple as using an if statement with a conditional format?

Massively appreciate your help.

Kind regards

Laurence

You're probably only seeing the "enable contents" warning because you've downloaded my version of the file from the internet and your security settings are set high enough to flag it.
Once you save the file to your system then reopen it, you shouldn't see that message (depending on your security settings there).

In the attached version I've added conditional formatting to highlight the relevant box in the grid.

BSB

Thanks again for your help - that has done the trick.

Much appreciated as that has been baffling me!

Happy to help

For the sake of neat and tidyness you can hide away the columns with the amber cells I've added.

BSB

Hi BSB,

Sorry to bother you again but I was hoping you might be able to help with another query? On the potential tab with the questions each question only requires one answer, a 'yes' in either 5,4,3,2,1. I was wondering if there is a way that the spreadsheet can notify the user if they miss a question out or answer yes too many times for one question as this could skew the results?

Thanks again,

Laurence

What about using conditional formatting to highlight each row green when completed and red if too many have been completed, but left white if not completed at all?

Also you could change the formula that calculates the score at the bottom to only show a score if all required rows have been completed correctly.

BSB

I think the conditional formatting would work as its just a reminder for the individual - sorry to be a pain but how would i do that?

Thanka again

No problem at all. Here to help

See attached. Conditional formatting added to count the number of Yes responses under the 1 to 5 columns in each row.
If one yes flag it as green, if more than one yes flag it as red.

BSB

Thats great thanks again for your help

Glad to be of assistance.

Give us a shout if you need more help.

BSB