Compare Performance to Yes-No Conditions in Excel Charts

When you see changes in trends of business performance, it’s natural to ask what caused them.

Excel charts can answer many of those questions by allowing you to compare performance to multiple Boolean (yes-no) conditions.

In an international company, for example, you could visually interpret a chart of sales that could be affected by recessions in several major markets.

Similarly, you could visually evaluate the effects of several ad campaigns that partially overlap. Or you could evaluate the effects of multiple hurricanes, floods, blizzards, or other weather emergencies in a region.

This figure shows one way to do this:

This chart definitely gives us more information to consider. Notice that:

• Dell’s revenues fell in 2000 just about the time that the Big 4 European countries (France, Germany, Italy, and the UK) started their recession.
• Revenues started to rise when the recessions ended in the US and in the Major 5 Asian countries (China, Japan, India, Indonesia, and Korea).
• When the Big 4 European recession ended about July of 2003, Dell’s revenues jumped.
• About December of 2004, in the middle of the Asian recession, Dell’s revenues flattened out, and stayed that way until the Asian recession ended.
• In 2008, just after all three areas had started their recessions, Dell’s revenues fell sharply.
• In 2009, when the recessions ended, Dell’s revenues rose sharply.

Of course, I might have over-interpreted the chart, but it definitely provides more information to work with.

The Source Data

The recession data comes from the Federal Reserve Bank of St. Louis, Series IDs USARECM, 4BIGEURORECM, and MAJOR5ASIARECM. These series return 1 for months with recessions, and 0 otherwise. You can download the data and paste it into your workbook, or you can use Power Query for the job.

The data looks like this.

With the data available, we now can stage it for the chart in a table like this:

Column A contains date values. Column B contains Dell’s sales that I downloaded using an add-in that’s no longer available. The sales shown are quarterly sales divided by three to provide a preliminary quarterly estimate of monthly sales.

Column C has a formula like this for the cell shown:

C7:    =TEXT(A7,”mmm”)&CHAR(13)&TEXT(A7,”yy”)

This column contains dates that will be used in the chart. The formula includes the carriage-return character—CHAR(13)—which causes the year to wrap below the month in the chart’s X axis.

Column D contains the final estimate of monthly revenue by calculating a centered moving average of the values in column B. It uses this formula throughout:

D7:     =IF( ISTEXT(D6), B7, AVERAGE(B6:B8) )

(Of course, using any average like this means that the quarterly sums of monthly sales don’t precisely equal the quarterly sums. But the totals are close enough for our purposes.)

Because we’re display three sets of recession data, column E returns the value of 1/3 if the country data referenced in cell E5 is in recession for the current month, otherwise it returns a zero.

To be clear, if we had wanted to display four sets of recession data, we would have divided by 4 in this formula:

E7:    =SUMIFS( xt.FredData.Value, xt.FredData.ID,E$5,  xt.FredData.Date,$A7 ) / 3

Then, this formula switches the logic of the formula in cell E7. That is, it returns 1/3 if there’s no recession and 0 if there is a recession.

F7:    =IF(E7=0, 1/3, 0)

I copied the formulas down their columns as needed. Then I copied and pasted the pair of columns to the right for the other two regions. Finally, for easy reference, I filled their areas with colors similar those used in the chart.

Now let’s take a slightly different view of the staging table:

Notice that the sum of any row of the six columns with a color fill always total 1, whether or not a recession occurs.

Creating the Chart

Here’s the same chart again, but with one minor change. The invisible columns now have a gray color.

Here are the five key things you need to know to create the first chart:

1. Each area that marks a recession requires two data series, not just one. That is, the three columns you see in the chart require six data series to display.
2. The first series of each pair shows a number when the recession occurred; the second series shows the number when the recession did not occur.
3. The chart type for all recession indicators is Stacked Column.
4. In the first chart, the second series of each pair has a fill color set to No Fill. That is, whether the second series equals 0 or .333, the first chart plots an invisible column. And the second chart plots a gray column. But because the column heights always add up to 1.0, the stacked columns always are the same height—whether there are recessions or not.
5. The columns have a 100% overlap, 0% gap width, and are plotted on the Secondary Axis.

Finally, to create the legend, I just set up four text boxes, added the text, shaded them, and moved them into position as shown.