In my course this week, I was able to discuss everyone’s Excel challenges and successes with them. During one of my discussions I was asked about a chart I am calling a quadrant chart. A quadrant chart is, in the example, I am discussing here, a chart with Sales on the X axis and Profit on the Y axis. In fact it’s a chart of a recent version of the Forbes 2000 list of the world’s largest 2000 companies.

The quadrant chart looks like this:

quad_chart_1

 

The quadrant is made up of two lines: sales and profits using this function: =PERCENTILE.INC(sales,F4) for sales and the equivalent for profits. That is, the quadrant in this example shows the 80th percentile of the sales and profits.

I have designed the spreadsheet to be able to show the quadrant at the first percentile, the 50th percentile … any percentile you like. For example, here is the quadrant at the 95th percentile for sales and profits.

quad_chart_2

The purpose of the quadrant is that it shows us the following:

quad_chart_3

 

How do we decide which percentile to use: that’s up to you. You can set it at any value you like to assess the sensitivity of your data. In the example here of the Forbes 2000, you can see there are very few companies in the top right hand corner and the majority of companies are found in the bottom left hand corner.

All this quadrant chart does is to provide visual clues as to the distribution of results.

There is a PDF version of part of this spreadsheet to download by clicking this link: quadrant_percentile_chart

 

Duncan Williamson

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: