Monday Morning Mulling: May 2018 Challenge

On the final Friday of each month, we set an Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

Final Friday Fix: May Challenge Recap

On Friday, we set up the scenario where you wanted to create a chart – say, a line chart – where you only wanted one element (line) to change. Using the following chart data,

you were asked to put the following chart together with an associated Slicer:

So far, so good, but the Slicer should only affect the Actual line item (in red, with the black marker in our example). For example:

or:

or finally:

Do you see how the chart title changes automatically?  That was part of the challenge too! 

Suggested Solution

As always, there’s always more than one way, but here’s our approach.  You can follow along with the attached Excel file.

First of all, our data has been placed in the following cells (shown just so you can follow the formulae):

Before I play with this, I am going to highlight this entire table (cells F52:I64) and created a PivotTable (ALT + N + V), or go to the ‘Insert’ tab on the Ribbon and click on ‘PivotTables’ in the ‘Tables’ grouping) showing the months only in rows as follows:

If you want a refresher on creating a PivotTable, please read our article on the topic >here. I have to have a PivotTable or a Table to create a Slicer. I tend to fall back on PivotTables (even though they are slightly more complex) as you can have one Slicer control multiple PivotTables which isn’t possible with multiple Tables.

Next, I add a Slicer for the ‘Months’ field (ensure your cursor is situated in the PivotTable and then use the keyboard shortcut ALT + JT + SF, or else select ‘Insert Slicer’ from the ‘Filter’ grouping on the contextual ‘PivotTable Tools’ tab ‘Analyze’ on the Ribbon). Again, if you need a refresher on Slicers, please click here.

Since the Slicer has originated from the created PivotTable, changing the Slicer, e.g.

automatically manifests the same modifications in the source PivotTable, viz.

I can use this relationship to create a modified data table:

Columns F, G and H are based on the source data, but columns I:K have been calculated differently:

• Starting with column J (rather than column I), this column contains the formula:

• in row 95.  The months only occur once in the PivotTable, so this COUNTIF function counts one (1) if the month is present in the PivotTable (i.e. the month was selected in the Slicer) and zero (0) otherwise
• Column I contains the formula 

which references the corresponding actual data for the month provided the value in column J is TRUE, i.e. any value other than zero.  Since I have already explained the only other value is 1, this formula is including the actual data if the Slicer has selected the month and puts #N/A otherwise.  Whilst prima facie errors are usually discouraged in spreadsheets, in this case #N/A causes the value not to display in the chart at all.

It should be noted you might have considered the SUBTOTAL or >AGGREGATE functions instead, but as complex as they are, if the PivotTable were to be hidden, the wrong results might occur.  Sometimes, simpler is better! 

The cells F94:I106 are all that are required to display the chart.  Simply select this range and create a line chart (ALT + N + N), or else select a lie chart from the ‘Charts’ section of the ‘Insert’ tab of the Ribbon):

• Finally, column K is required to assist with the chart title, which I haven’t yet explained.  The formula here,

keeps a running total of all the months displayed (i.e. the first month selected is 1, the second is 2, etc.). This is useful as the number 1 relates to the earliest month and the maximum value relates to the last month selected. This latter number also represents how many months have been selected too.

In fact, the labelling requires several preliminary calculations, viz.

Let me go through them.

The formula in cell J115, ‘All Months Selected?’,

checks to see whether the total of all of the 1’s from column I (above) equals the number of rows in the range.  This can only happen if all months have a ‘1’ allocated to them, i.e. all months have been selected.

The formula in cell J116,

checks that one and only one month has been selected.

Cells J117 and J119 contain similar formulae.  For example, the formula in the former cell,

returns the name of the first month selected chronologically (the other formula identifies the last period selected).  This uses the INDEX MATCH combination which we have explained previously >here.

Cells J118 and J120 use similar calculations too.  The first formula

returns the corresponding month number (which is what the other formula does too).  Therefore, the formula in cell J121,

determines how many months would be included if all months between the first and last months had been selected.  This is because the final formula in cell J122,

checks whether the number of months selected equals the number of months in total between the first and last months previously selected. If a month or more is missing, the result will be TRUE instead of FALSE.

These interim calculations are used as follows to create the chart title all in one cell:

Cell J126 contains the formula

This concatenated formula will display the details required, e.g. “Comparative Actual Data for All Months or “Selected Actual Data for Aug”).

To get the formula from cell J126 into the chart itself, add a chart title, and while it is selected, click on the formula bar, type ‘=’ ad then click on cell J126.  Don’t type the formula =J126 or =Example!$J$126 in.  In some versions of Excel, for this to work, the cell has to be selected on the sheet for the title to become formulaically dynamic.

Simple (sort of)! 

The Final Friday Fix will return on Friday 29 June with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.