Dynamic Performance/Potential 9 Box Grid in Excel

mcdonaldalan

New Member
Joined
Dec 18, 2009
Messages
4
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Welcome to MrExcel.

You could use an XY Scatter Chart with Grid Lines and some Text Boxes. You would need to calculate the XY coordinates from your data.
 
Upvote 0
Hello Andrew, I'm not sure that's going to work - each point or bubble on the chart has to be independent e.g. if there are 9 people then there are nine dots/points/bubbles and each one should be in a certain box in the matrix. Changing the number moves the dot/point/bubble to another box in the matrix. Am I making sense or do I sound a bit mad?
 
Upvote 0
A Chart changes if you change its source data. The number of people isn't related to the number of boxes is it?
 
Upvote 0
Andrew, FormR, that's exactly the kind of thing I'm looking to do. I'm so impressed! To see it done in excel is very exciting for me. I have not seen the formulas below before would you mind giving me an overview of the construction or perhaps point me to a resource where I can learn more about them.

Thanks again, I'm very impressed.

=C2+(5*MOD(B2-1,3))+MOD(B2-1,3)


=(B8-(ROUNDUP(B8/3,0)-1)*3)+(0.1*COUNTIF($B$2:B8,B8))
 
Upvote 0
The MOD function returns the remainder after its first argument is divided by its second argument.

This is great stuff and just what us frustrated HR people are looking for. I've been playing around with Andrew Poulsom's version as it incorporated the 'legend' so I could track indivdiuals within the scatter plot itself.

One additional question or request, considering that we may be using this for the whole company (150 employees), there's going to be, I would imagine, a considerable amount of 'stacking' of the data points in this model. For example, if we were to 'rate' everyone in the top right quadrant, the scatter plot data points begin to stack on top of each other. Is there a way that this smaller population can be expanded to accommodate a larger population to eliminate the stacking issue?

Once again...awesome work guys on this!!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top