Dynamic Performance/Potential 9 Box Grid in Excel

[mcdonaldalan]

I don't know if this is an easy thing to do. It could be worthy of a duelling podcast between Mr.Excel and Exelisfun . I've attached a link to a source file that shows an example of what I'm aiming to do. Basically a 9 box grid exists. If a person in the list below is given a number between 1 and 9 a 'dot' shows up on the grid. If the number changes then the dot automatically moves to the new box number.

I'd appreciate any feedback on how to do this or if it can even be done. I'm stumped.

Thanks in advance,

Alan.

https://dl.dropboxusercontent.com/u/2329152/9 box grid mock up.xlsx

 

[Andrew Poulsom]

Yes both axes run from zero to 18. Actually there are three groups of 5 (the calibration range) on each axis, as nothing is ever plotted on the gridlines. The layout of the boxes is:

7 8 9
4 5 6
1 2 3

The box that the person appears in is determined by the values in column B. The vertical and horizontal position within the box is determined by the entries in column C. Formulas in columns C and D are used to calculate the position given the axis values used.

It sounds like you don't need a 9 box grid at all, but just an XY Scatter Chart. If you want to post some sample data and explain how you want the chart to look I'll see if I can help you.

 

[miamianderson]

Thank you for the assistance... I'll play with it some more and come back crying if I need additional guidance.

 

[WesleyV]

This is a great tool but I'm struggling to set it up to meet my needs. Like MiamiAnderson, I want to be able to enter a performance score (1, 2, 3, 4, or 5) and a potential score (1, 3, 5) and have it plot into the appropriate box as one "point" per person. I understand how you've set up the current version to plot, but it doesn't allow me to have a performance and potential score input and then plot automatically. Here's what I mean: For instance, Joe is a 3, 5 score, Sue is a 5, 3 score, and Bill is a 1, 3 score. Using the nine box, Joe would be in box 8 (medium performance, high potential), Sue would be in box 6 (high performance, medium potential), Bill would be in box 2 (low performance, medium potential). I would love to set up the sheet so I could have the columns of name, performance score, potential score and then have it plot correctly using your format. Any ideas? Thanks - this thread has been great to read through...!

 

[Karthik_sadagopan]

Hi Andrew,

Would you be able to help me with the grid you shared, it has only parameter. Below is the dataset I need to plot in a 9 grid chart, plotting the current and future state.
Current state Future state

Leaders 1 3
Data 2 3
Technology 3 3
Mis 1 2
Others 2 3

 

[Karthik_sadagopan]

Would be keen to know if this is feasible in your model Thank you in advance