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

 

[udayn9]

Does this formula work for you?

=LOOKUP($B2,{1,2,3,4,5,6,7,8,9},{9,7,4,8,5,2,6,3,1})

Thanks a ton

It worked.

 

[udayn9]

Andrew thanks again for the 9 box sample you had posted earlier and the formula you shared. This is now a super hit in my organization.

The sample provided is for a ideal size for populating 20 to 25 employees. If I try to add more employees like 50+, there is a risk of overflow to the next box.

For example - If I rate 15 employees on box "5", 10 employees appear within box"5" the rest overflow into box "9"

Is there any solution for the issue of overflow? can you provide a code to fit in more employees in one single box. Kindly advice.

 

[Andrew Poulsom]

Can't you reduce the 'nudge' factor? It's 0.25 in my example.

 

[miamianderson]

I'm excited about this possible solution to a similar challenge I have... is there a place other than "app.box.com" where I can download the file? I am unable to access their site.

 

[Andrew Poulsom]

How about Dropbox?

 

[miamianderson]

How about Dropbox?

That would be great!

 

[Andrew Poulsom]

Can you access this?

https://www.dropbox.com/s/izdt9oq0xqjl26o/9BoxGrid.xlsx?dl=0

 

[miamianderson]

I can- Thank you! It looks just like what I've been seeking... I just need to review the above comments now to get a better understanding of how it works. I'm sure I'll have some follow-up questions for you. Many thanks!

 

[miamianderson]

The appearance of the output is exactly what I've been looking for... however I'm not clear some of the logic of the design nor on the input. (Apologies for my remedial Excel skills!) Is your grid presuming an 18-point scale for both axes? If so, how do I adjust it? It seems that the way this was designed was as if we knew what box they should land in first, and allows us to tweak their placement with the "calibration" column. (What is the function of the "Calibration" column?) How could we instead set it up to accept a performance score (1-5) and potential score (1-5)?

 

[miamianderson]

(You don't have to answer the last question- I've got that now. Just need guidance with the input.)