Formula Forensics No. 032 – Creating Dynamic Charts with Non-Contiguous Data

Formula Forensics No. 032 – Creating Dynamic Charts with Non-Contiguous Data:
I have lost count of the number of articles that discuss Excel charts.
There are also quite a few that discuss creation of dynamic charts (where the data to be plotted is determined dynamically).
However, most of the articles assume that the data to be plotted is organized neatly in adjacent cells. But what if you wanted to plot data that is not contiguous?
In this article, we will learn how to plot non-contiguous data. If you can identify an algorithm to locate your relevant data, we should be able to plot it, regardless of where the data is located!

Background Information

The following articles provide good background information on dynamic charts:
Jon Peltier’s article: http://peltiertech.com/Excel/Charts/DynamicCharts.html
Debra Dalgleish’s (Contextures) article: http://www.contextures.com/xlNames02.html
Chandoo’s article on this site: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
This article assumes that you are already familiar with how to create an Excel Name, and how to assign a Name to a dynamic chart. If you need a refresher, please read the above referenced articles.
As with all Formula Forensic posts you can follow along using the sample files: Excel 2007+; Excel 97-2003

Data Setup

For the purposes of illustrating the dynamic chart, I have setup the data as follows:
Column A contains some date values
Column B determines the weekday (Sun, Mon, Tue, etc.) for the dates in Column A
Column C, D, E, F are data values corresponding to the dates in Column A. These will be the four data series that we will plot.
Column G contains text values that will be used as x-axis labels for the chart
A basic chart displaying this data is shown:

Obviously we cannot see which Data is from Monday (Day 1)
To make the chart dynamic, we will plot the four data series only when the weekday value = 1 (denoting Sundays).

(The rows where weekday=1 have been highlighted for ease of reference.)

The formulas that make the chart dynamic

Create the following Names to make references simple:
WeekDayList refers to the range B2:B23
Series1FullList refers to the range C2:C23
Series2FullList refers to the range D2:D23
Series3FullList refers to the range E2:E23
Series4FullList refers to the range F2:F23
LabelsFullList refers to the range G2:G23

If we needed to get the sum of all values in Series1 where WeekDay=1, we would use a formula similar to the following:
=SUMPRODUCT((WeekDayList=1)*Series1FullList)
The argument to the SUMPRODUCT formula resolves to the following array:
={10;0;4;0;0;0;8;0;0;0;0;0;0;5;0;0;0;0;0;3;0;0}
As you can see, there are a lot of zeros for those data points where the WeekDay was not equal to 1.
If we were to plot this array, Excel would faithfully plot the zero values also.

or hiding the Zeroes as:

These charts show the Values but are not ideal.
Some of you might be thinking that you can just substitute zero values with #NA, and Excel will not plot the #NA values.
You are correct. That is one approach to not plotting the zero values.


However, since that is just tricking Excel into ignoring some values (even though the #NA values still exist), we will look at a different approach that truly condenses the array to the non-zero values.

The Technique

First, let us build a helper formula that provides the position of the data where WeekDay=1
SubsetIndex: =SMALL(IF(WeekDayList=1, ROW(WeekDayList)-ROW(INDEX(WeekDayList,1))+1), ROW ($A$1 : INDEX ($A:$A, COUNTIF (WeekDayList, 1))))
Looking at the parts of this formula
ROW(WeekDayList)-ROW(INDEX(WeekDayList,1))+1 creates a sequential array from 1 to the number of rows in WeekDayList:
={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22}
IF(WeekDayList=1,…) produces an array with the sequential values above when WeekDay=1, and FALSE otherwise:
={1;FALSE;3;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;14;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;FALSE}
COUNTIF(WeekDayList,1) returns 5 in the above sample data, indicating that we have five rows where WeekDay=1
ROW($A$1:INDEX($A:$A,COUNTIF(WeekDayList,1))) produces a sequential array from 1 to the number of rows where WeekDay=1:
={1;2;3;4;5}
SMALL(…) extracts the 5 smallest values from the IF(…) array:
={1;3;7;14;20}.
Please note that these values indicate the position in the WeekDay list. i.e. the relevant positions are rows 2, 4, 8, 15 and 21 (to account for the headers in row 1).

Now use the SubsetIndex to extract the subset of relevant values in Series1FullList:
=N(OFFSET(Series1FullList,SubsetIndex-1,0,1,1))
We will give it the name Series1SubsetData
Use SubsetIndex-1 because the first row for the OFFSET function is row zero.
The OFFSET function resolves to OFFSET(Series1FullList,{0;2;6;13;19},0,1,1).
i.e. we are asking the OFFSET function to extract the rows indicated by {0;2;6;13;19} in the same column (zero) as Series1FullList, that is 1 row high and 1 column wide.
The OFFSET(…) function returns the array
={10;4;8;5;3}
It would have been straightforward if this returned array was usable as is. However, unless you array-enter this formula over 5 rows, you would not be able to use it anywhere else. (For example, if you check ROWS(OFFSET(…)), you would get the value 1 indicating that Excel is hiding the rest!
That is where the N() function comes in handy.
While the common use for this function is to convert text values into numbers, it also has a hidden talent for converting the references returned by OFFSET into an array.
The N(…) formula returns the following for our data set: {10;4;8;5;3}.
Even though it looks just like the output from OFFSET function alone, it has now become usable for our purposes!
For example, if you use ROWS(N(…)) you will get 5 as expected!

Hint:

Please note that there are other ways to eliminate the zero values from an array. For example, please see the following recent post and responses:
http://chandoo.org/forums/topic/dynamically-generate-contiguous-data-in-array-for-chart-from-non-contiguos-range

We will setup the following Names and formulas for the rest of the data series:
Series2SubsetData =N(OFFSET(Series2FullList,SubsetIndex-1,0,1,1))
Series3SubsetData =N(OFFSET(Series3FullList,SubsetIndex-1,0,1,1))
Series4SubsetData =N(OFFSET(Series4FullList,SubsetIndex-1,0,1,1))

Labels

While the trick with the N() function works with numeric data, it does not work with string data. Since we would like to use the text from LabelsFullList, we will need to use a helper column to extract just the text strings that correspond to the condition WeekDay=1.
There are many ways to extract a text subset, but I have used the following formula in cell J2 and copied down to J23.
=INDEX(LabelsFullList, INDEX(SubsetIndex,ROW(1:1)))

We can ignore the error values since we will only be using the subset that returned text values. We will use the Name HelperColForLabels to refer to cells J2:J23
To refer to just the text values, we will use the following formula, and give it the name XAxisLabels
=INDEX(HelperColForLabels,1):INDEX(HelperColForLabels,ROWS(SubsetIndex))
The above formula resolves to ={“Dynamic”;”labels”;”are”;”really”;”easy”}

Creating the Dynamic Chart

Now that the formulas are in place, all we have to do is reference them in an Excel chart!

For the Chart,
Series1 values refer to =DynamicCharts.xlsx!Series1SubsetData

Series2 values refer to =DynamicCharts.xlsx!Series2SubsetData

Series3 values refer to =DynamicCharts.xlsx!Series3SubsetData

Series4 values refer to =DynamicCharts.xlsx!Series4SubsetData

Horizontal (Category) Axis Labels refer to =DynamicCharts.xlsx!XAxisLabels
The end result looks as follows:

The chart and axis labels adjusts to changes in values in the underlying data, as expected.

Final Thoughts

We have used an innocuous looking function N() to make the chart dynamically adjust to non-contiguous data. (There are other ways to handle dynamic data… but that is for another article!)
Do you know of any other Excel functions with hidden talents?
Please write and let us know in the comments below:
In the meantime, I wish you continued EXCELlence!
Sajan.

Thank You

This was Sajan’s third post at Chandoo.org and so a special thank you again to Sajan for putting pen to paper to describe this great technique here.
If you like this technique, you may want to thank Sajan in the comments below:

Formula Forensics “The Series”

This is the 32nd post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like Sajan has done above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.