Using Area Chart for percentile curves 1

[jkupov]

I've been fighting excel all day and am having no luck!

I've ranked our call center employees based off of their service level and have used the percentile function to calculate where each employee falls in relation to the performance of the total group.

I want to be able to provide their supervisors with a percentile chart that shows the employee's individual performance over the past 12 months laid over shaded curves representing the percentile range. (I hope I'm making sense)... so I want the 10th, 25th, 50th, 75th and 90th percentile shown on the chart as shaded areas and then have their individual performance shown as a line progressing through the percentile curves as the months progress.

That sounds awful confusing...

OK. I want my y axis to be the high end of the percentile range and my x axis to be the low end... so for the 75 percentile there will be a shaded area from 75 on the y axis to 89 on the x axis... then the same for the other percentiles. This should create a curved fan area on the chart and the individual performance (a third axis) would proceed horizontally following the past 12 months.

Hmmm... I can attach a file if that is permitted. My doctor uses a chart similar to this, which is where I got the idea. However, I remember reading some poor guy's post who got blasted for attaching a file because some other guy said that he didn't want to get a virus. Should I attach an image file or is that bad form?

 

[jkupov]

I didn't think of this before, but the site that my doctor and I use to chart my progression has a sample image of their percentile curves. I'll provide the link below and you can decide to go to it or not. It isn't a fly by night website- the company is highly regarded in the medical community so you don't have to worry about what you might find by following the link.

Here is an example of what I am thinking I want it to look like... only adapted for the workplace:

http://blog.patientslikeme.com/wp-content/uploads/2009/08/pls-patient-on-als-curves_ls2-1024x848.jpg

 

[jkupov]

I just realized I've been thinking about this all wrong which is why I haven't been able to wrap my head around it all day. I need the shaded percentile curves to follow the progression through the same time period (12 months) as the individual emplployee's performance. The curves won't be as pretty as the image at the provided link because there isn't a steady decline or increase in the percentiles like a declining medical condition would provide. So I need to follow the 90th percentile over 12 months and shade that area using an area chart...

But I'm still stuck there. Probably because I need to shift my mind away from how I've been thinking about it thus far. Maybe I just need some help getting started. Thanks!

 

[SkipVought]

Hi,

Make your percentile bands using a stacked area chart type on secondary axis.

Make the primary axis your data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jkupov]

Thanks Skip,
I guess I'm not quite understanding. So for my percentiles I have the following:

A B

1 10th 73.0%
2 25th 74.8%
3 50th 77.8%
4 75th 81.0%
5 90th 83.2%

For the employee which I selected for this example I have the following data:

A B
8 Oct-08 73.9
9 Nov-08 70.03
10 Dec-08 75.7
11 Jan-09 79.8
12 Feb-09 79.0
13 Mar-09 78.1
14 Apr-09 81.1
15 May-09 81.4
16 Jun-09 81.8
17 Jul-09 82.1
18 Aug-09 82.4
19 Sep-09 83.1

But when I insert the area chart it is just blank.

For my Legend Series I select $a$1:$B$5. This puts the percentages on my y axis with a colored series in the Legend- good. But where are the shaded areas for each precentile range?

 

[SkipVought]

10th 73.0%
25th 74.8%

So you want to 10th to be >= 73 and < 74.8?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

something like this might work. You'll have to tweek your percentile boundaries, thought, I think...
[tt]
73 74.8 77.8 81 83.2

dat amt 10th 25th 50th 75th 90th
8-Oct 73.9 73 1.8 3 3.2 2.2
8-Nov 70.03 73 1.8 3 3.2 2.2
8-Dec 75.7 73 1.8 3 3.2 2.2
9-Jan 79.8 73 1.8 3 3.2 2.2
9-Feb 79 73 1.8 3 3.2 2.2
9-Mar 78.1 73 1.8 3 3.2 2.2
9-Apr 81.1 73 1.8 3 3.2 2.2
9-May 81.4 73 1.8 3 3.2 2.2
9-Jun 81.8 73 1.8 3 3.2 2.2
9-Jul 82.1 73 1.8 3 3.2 2.2
9-Aug 82.4 73 1.8 3 3.2 2.2
9-Sep 83.1 73 1.8 3 3.2 2.2
[/tt]
All the percentile columns except the 10th, are calculated from the top row as the differnce from the previous.

Create the chart using the Chart Wizard. Set the Chart Type to Stacked Area

Select the first series and change the Chart Type to LINE.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jkupov]

OK. .. Good. I always have a hard time understanding charts. I prefer lines of data

So I'm not sure if Excel 2007 has a chart wizard. I usually just select my data, go tot he insert tab, chart group and select my chart type. If I do that, I end up with the color representing the amt series on the very botom occupying 0% to roughly 48%. then the 10th percentile color occupying roughly 48% to roughly 95% and the rest of the colors all squished together at the top.

If I click on the amt series in the legend and change the chart time to line, I get a line at the very top from roughtly 7500% to roughly 8300%... and all the percentile colors squished at the very bottom.

 

[jkupov]

OK I adujsted the axis options tp scale to the area I want to see ( 60-90%) and adusted the numbers to the following. The only thing I can't get now is my trend line:

date amt 10th 25th 50th 75th 90th
Oct-08 73.9 27 25.2 22.2 19 16.8
Nov-08 70 27 25.2 22.2 19 16.8
Dec-08 75.7 27 25.2 22.2 19 16.8
Jan-09 79.9 27 25.2 22.2 19 16.8
Feb-09 79 27 25.2 22.2 19 16.8
Mar-09 78.1 27 25.2 22.2 19 16.8
Apr-09 81.1 27 25.2 22.2 19 16.8
May-09 81.4 27 25.2 22.2 19 16.8
Jun-09 81.8 27 25.2 22.2 19 16.8
Jul-09 82.1 27 25.2 22.2 19 16.8
Aug-09 82.4 27 25.2 22.2 19 16.8
Sep-09 83.1 27 25.2 22.2 19 16.8

 

[SkipVought]

Thats because you data is not normalized. Your amt is a different value range than your percentiles.

Your percent values are probably values less than 1 FORMATTED as percent.

Your amount values are values greater than 1, like 79. 79 does not equal 79%!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jkupov]

Right.. heh heh... thanks. Perfect.