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

 

[mcdonaldalan]

Andrew, this is really great. I've tried for a list of 30 people and it's working (no stacking). I'll be trying it out later today or tomorrow with a larger list. I'll update the board with the results.

P.S. Is it wrong that I'm really excited by this?

 

[Andrew Poulsom]

Is it wrong that I'm really excited by this?

There's nothing wrong with being excited about Excel's capabilities.

 

[tmorselli]

In the version below I have tweaked the y axis values slightly when there is a tie:

https://app.box.com/s/31w0t1k4k2c50qniytyh

Awesome, works great. One last request, is there a way to make this slightly more expansive? The challenge I'm having is that my employee pool is approximately 100 people in total. When I add users to Column A and pull down the formula it doesn't work (for me, at least). Can additional rows be added easily up to 100?

Appreciate this effort more than you know...incredible work!!

 

[Andrew Poulsom]

In what way doesn't pulling down the formulas work? You will have to add additional people to the chart as new series.

 

[udayn9]

Dear Andrew, This code is great. However, I have a slightly different issue. The 9 box grid positions we follow is different and I am not sure how I should I go about plotting people as per the grid setup we have.


6 3 1


8 5 2


9 7 4


Please help me here.

 

[Andrew Poulsom]

There doesn't seem to be any logic for those positions so I don't think I can help, sorry.

 

[udayn9]

There doesn't seem to be any logic for those positions so I don't think I can help, sorry.

Thanks for the quick response andrew.

Is it possible to write a code to the cell where the values are put in like


read 1 as 9, 2 as 7 etc...?

 

[Andrew Poulsom]

Please post some representative sample data and the expected results.

 

[udayn9]

Please post some representative sample data and the expected results.

So here is what I did, I enetered the below code where the Box Number is enetered (Ref Scatter chart file you uploaded)

=IF($B2=0,"0",IF($B2=1,"9",IF($B2=2,"7",IF($B2=3,"4",IF($B2=5="5",,IF($B2=6,"2",IF($B2=7,"6",IF($B2=8,"3",IF($B2=9,"1",IF($B2<9,"0"))))))))))

But I am sure there is a flaw in this code since there is a conflict when I eneter the number "2". I am very new to this excel coding. Please help.

I am sorry I am blocked from uploading any file from my office

 

[Andrew Poulsom]

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})