• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automating an Excel Scatter Plot to Display Strength in Relationships

cmohnkern

New Member
Hello Chandoo,


I am attempting to setup a scatter plot that takes metrics and translates them onto a the scatter plot (automatically). The purpose of this diagram is to map the quality of our team’s relationships to other focal points. Below is a description of what I am trying to accomplish:


Each of our team members own their own scatter plot. So, for example, my scatter plot should show all of the relationships that I have (via dots to represent the different relationships).


The plot is divided into 4 square sections - 2 on top (or above the x axis) and 2 below (or below the x axis). These different sections represent different strategies for our team members’ relationships.


The scatter plot is also divided in three circular sections stemming from the origin (where the x and y axis intersect). These three circles encompass one another starting with the smallest in the center (like nesting/matryoshka/babushka dolls).


The smallest circle (closest to the origin) is meant to represent the closest relationships (or level 1 relationships).


The second circle fits around the level 1 circle and is meant to represent relationships that are not as close as the relationships in the center (or level 2 relationships).


The third circle (encompassing both the level 1 and level 2 relationships) is meant for level 3 relationships (the least close relationships).


Each of the plots on this scatter graph should represent a relationship. So, for example, if ‘Relationship 1’ is a level 1(very close) relationship and is within my strategy 2, the dot representing that relationship should end up in the most center circle and in quadrant 2. For example, if ‘Relationship 2’ is a level 3(least close) relationship and is within my strategy 4, the dot representing that relationship should end up in the least center circle (furthest from the origin) and in quadrant 4.


I want to be able to automate this so that our team members could go into an excel sheet, for each relationship, indicate the level of close-ness and what strategy that relationship should be aligned with, and this would cause the relationship point to be plotted on our scatter plot in it’s respective quadrant and circle (or closeness to the origin).


I am not very good with building diagrams in Excel and desperately need help in order to make this happen. Please let me know if you have any lessons that I might be able to use to help me to compose this tool. Any help would be greatly appreciated.


Thank you,

CoCo
 
CoCo


I have mocked up what I think your after


Have a look here: https://www.dropbox.com/s/uw4vho4w8traddm/People%2BCircles.xlsx


I have setup three groups that are color coded

You can add people if required just keep adding people below the existing ones

You can also just delete people

The People Group areas are Conditionally formatted to match the Chart data


The 3 Circle Sizes can be adjusted in the 3 Green Circle 1 - 3 cells


The entire chart is driven by named Formulas
 
You are AWESOME! Thank you so much for your help. This has made a WORLD of differece. I will let you know if I have any further questions.


Thank you again!!
 
I am having some difficulty doing the following changes:

-I have been trying to change the coordinates of the people. I have been trying to change it under their respective 'People Groups' but my changes are not reflected in the scatter chart.

-I would like to add another people group so that there are a total of 4. How would I do this?

-I would like for the x and y asises to be 0-3 (not 0-60).


Any insight into how to make these changes would be greatly appreciated.


Thank you,

CoCo
 
CoCo


https://www.dropbox.com/s/fp59ej1rp7mpmsn/People%2BCircles2.xlsx


Each series in the Chart is derived from a Named Formula

You can see these if you go to the Formulas, Name Manager Tab

There is a Formula for the Names: eg: Names4

and a Formula for the X & Y values: Grp4_x & Grp4_y


These last two are based on the Names4 name and hence expand as you add/subtract people
 
Thank you for your help; however altering the Named Formulas is a bit over my head. Ideally, I would like to simply change the coordinates under the respective 'People Grps', and this would cause the dot representing that person to move. Right now, when I change the coordinates, nothing happens.


Further, my initial desire was to have all of the People Grp 1 inidividuals to be in quadrant one, all of the People Grp 1 inidividuals to be in quadrant two, all of the People Grp 3 inidividuals to be in quadrant three, etc. Right now they are all mixed together. Would there be a way for me to give, for example (working off of the diagram you have composed), John, a score of 2, and this would automatically move John's dot into circle 2 in quadrant 1.


Please help. Thank you!
 
Also, instead of displaying "cellrange" next to the various dots on the diagram, would it be possibel to have their respective names?


Thank you!
 
Back
Top