• 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.

Scatter Chart... is this possible?

W

William Hulme

New Member
Hi all!

This is my first question so please be gentle if I seem a bit green, omit something or if I'm unclear in any area.

I am looking to create a scatter chart but nothing I do is working out anything close to what I am trying to achieve and I'm in need of help and guidance.

The purpose of the chart is to show people who and their ratings and tenure.

The data used would be:-

- A group of say 15 people
- A time period of 12 weeks
- A performance rating ranging from +100 to -100

Ideally what it would look like is as follows:-

- A scatter chart that is divided in to quartliles
- The quartiles would represent tenure over the 12 week period so top left could be
weeks 1-3, top right 4-6, bottom left 7-9 and bottom right 10-12
- The axis would run both vertical and horizontal through the centre. The centre point
would be a value of 100 and this would decrease in increments of say 20 until it
reaches the value of -100 at the outer part of the chart.
- The people being measured would then be the dots on the graph.

The chart produced would help me to identify people who need assistance as they would highlight as outliers with those not needing any help would bunch in the middle irrespective of tenure.

Is this at all possible and if so please could somebody provide me with some guidance as to how?

Many thanks for looking

Will


_________________________________________________________________

Post Moved by Moderator.
 
Last edited by a moderator:
Hi ,

That is an excellent description of your requirement.

What is now required is your data , in the form of a workbook.

Once you upload your workbook with data in it , hopefully your problem will be resolved at the earliest.

Narayan
 
Hi Narayan,

Thank you for such a prompt reply.

I've attached a spreadsheet showing what I believe the data will look like when I am presented with it.

I should just get people's names together with their tenure and rating.

Kind regards

Will
 

Attachments

  • Scatter Graph.xlsx
    8.6 KB · Views: 8
Hi ,

I am sorry , but I am not able to understand your data in relation to your original post.

1. What will the X-axis represent ?

2. What will the Y-axis represent ?

3. A scatter chart or X-Y chart has points which have two co-ordinates ; if you say that each advisor is represented as a point in the chart , then the two co-ordinates have to be the tenure and the rating. Will the tenure be represented along the X-axis and the rating along the Y-axis ?

Narayan
 
Hi Narayan,

It is probably easier for me to show you what I am looking for. That way you will be better placed to say it can or cannot be done as stands, or it can be done but only with changes.

I have made an illustration of what I am looking for. See what you think. Hopefully this will make more sense of the original post in conjunction with the data provided.

The dots on the chart are the people who are are split into quartiles by tenure and then by performance. In the illustration you can see how many of the people are performing well because they are close to the centre of the chart where the rating score is +100. You can also see 2 examples of people who need support. One of these has low tenure, the other, by comparison, has high tenure but both are featuring between the -50 and -100 rating indicating that they need help or training to enable them to perform better.

If this cannot be done then I would welcome any suggestions that you might have for something suitable.

Kind regards

Will
 

Attachments

  • Scatter Chart Illustration.pdf
    280.2 KB · Views: 9
Hi ,

I doubt that what you say can be done with just one Excel chart.

As far as I can see from your depiction , the rating is to be used to plot a point , and the tenure is to be used to select the quadrant.

If these quadrants are fixed , once and for all , it might be possible to derive an artificial value for the X co-ordinate and the Y co-ordinate so that we can achieve your depiction , but it would be an artificial way of achieving it.

Suppose we have a quadrant which represents the tenure 4-6 weeks ; can I assume that any tenure whether 4 , 5 or 6 will be represented by the same point depending on the rating ?

Narayan
 
Hi Narayan,

The plan at this time would be for the quadrants to be fixed to show periods in time spanning a total of 12 weeks as it would be the intention for this to monitor a training period from when trainees commence training to when they get ready to join the department.

Going forward this could change and it could be used to monitor an entire department of people who have longer tenures of up to 2 years albeit again divisible into quadrants.

With regard to your assumption, you are absolutely correct. It would not matter whether the person was in week 4, 5 or 6. The trainees have a "glide path" meaning that expectations in terms of their performance changes at certain points during training, so the expectation in week 4 is that same as in week 5 and 6. Progress is expected to have been made in week 7 from 6 and therefore the targets are higher.

Kind regards

Will
 
Hi Narayan,

Even if the concept is not possible I would like to thank you very much for your thoughts and attention.

Kind regards

Will
 
Hi Narayan,

That looks awesome, thank you.

Can I ask a question?

Where the points for advisers on the chart currently have a number because I gave you Adviser 1, Adviser 2 titles along the way, could these be substituted with names at some point or does that throw everything out?

It's not a problem if names can't be used because I can make reference to them elsewhere. It's just a thought.

It looks fantastic though! I'm so grateful for your help.

Kind regards

Will
 
Hi ,

The problem with names is that there will be a lot of overlap , since points may be clustered together.

Only if you increase the size of the chart so that it spans more than one screen can we ensure that the names do not overlap.

Narayan
 
No problem Narayan . I see your point. I think that I could keep a list of the names and how they correlate to the numbers to one side.

Thank you so much for all your help and assistance with this spreadsheet.

Kind regards

Andy
 
You must log in or register to reply here.
Back
Top