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:
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.
The purpose of the quadrant is that it shows us the following:
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