Dynamic charts with non-contiguous data

[Sajan]

Hi,

Could anyone provide any pointers for creating dynamic charts with non-contiguous data?


My data setup is similar to the following:

[pre]

A	B	A	B	A	B	A	B	A	B
Jan-12	Jan-12	Feb-12	Feb-12	Mar-12	Mar-12	Apr-12	Apr-12	May-12	May-12
1	10	2	20	3	30	4	40	5	50

[/pre]
i.e. the header row is row 1

the date row is row 2

the value row is row 3


What I would like to do is create a chart (e.g. line chart) with the dates as the X-axis, and two data series: one for the "A" columns, and the second for the "B" columns.


Based on examples I have seen on this site and elsewhere, I have been able to create dynamic charts where the data is contiguous. i.e. when the resulting data range is one single contiguous range, the chart gets plotted correctly. However, when the data range is selected from non-contiguous cells, I am finding that Excel plots only the first cell.


Essentially, what I am attempting to plot are the following two (dynamically created) series:

"A" series would be the array {1,2,3,4,5}

"B" series would be the array {10,20,30,40,50}


A simplified formula for generating the "A" series is:

=OFFSET($A$3,0,(COLUMN($A$3:INDEX($3:$3,5))-1)*2)


"B" series is:

=OFFSET($A$3,0,(COLUMN($A$3:INDEX($3:$3,5))-1)*2+1)


A simplified formula for generating the "date" values is:

=OFFSET($A$2,0,(COLUMN($A$3:INDEX($3:$3,5))-1)*2)


Any help will be very much appreciated.


thanks,

Sajan.

 

[Sajan]

Hi,

I was able to find a solution...


Apparently, one needs to force the results of the OFFSET function into numbers, such as

=N(OFFSET($A$3,0,(COLUMN($A$3:INDEX($3:$3,5))-1)*2))


This allows the non-contiguous data to be plotted correctly.


While researching the above problem, I noticed that wrapping the INDEX around the OFFSET formula above (such as INDEX(OFFSET(...), 1,0) returns only a single value, even though evaluating just the OFFSET function returns an array. Not sure why that is... but something to research further.


Cheers,

Sajan.

 

[NARAYANK991]

Hi Sajan ,


It's great that you have come up with your own solution.


Take a look at the earlier question , to which Hui posted a solution workbook , Sales_Solution.xlsx , created by Sam of the Excelhero forum.


http://chandoo.org/forums/topic/counting-no-of-rows-with-non-zero-totals-over-non-contiguous-columns-dynamically


Narayan

 

[Hui]

You may also want to look at: http://chandoo.org/wp/2011/11/30/formula-forensics-no-004/

Which describes a similar situation

 

[Sajan]

Hi Narayan,

Thanks for the link to the previous question, and the workbook from Sam at Excel Hero. I downloaded the doc, but will not have access to a computer with Excel for the next two days. As such, I will report back once I have tried it.


Based on the problem described by the OP there, it appears that the problem was different than mine. Since that OP was only interested in a count of the number of dealers that met a certain criteria, that makes the solution very straightforward.


In my case, I was attempting to get an array back from the formula, with each value in the array to be plotted in the chart. Since my knowledge of Excel charts is very limited, my understanding is that every value of such an array would get plotted, including the zero values from the cells that did not match the condition. Perhaps there is a setting in Charts that I am overlooking, where I could ask that only every 2nd or 3rd value be plotted, to skip the zeros.


Hopefully, Sam's workbook offers some clues to answer my questions above, and I will report back.


Hi Hui,

Thanks for the link to the FF article. However, since multiplying by the MOD function results in an array like {1,0,1,0,1,0}, it still has the problem that I describe above, where the zero values will get plotted by the Chart. Perhaps my limited knowledge of Excel charts is causing me to overlook a setting where every other value in an array can be skipped from being plotted.


Since the OFFSET based formula in my original post returns an array with only the values that I want, I am able to do what I need. However, I will report back with any other solutions that would also apply.


Thanks!


- Sajan.

 

[Sajan]

Hi Narayan,

I got a chance to try the solution created by Sam from ExcelHero.


Unfortunately, that solution is geared towards addressing a different problem: counting the cells / rows that match a criteria. The OFFSET based solution I outlined in my previous post appears to be my best option at present.


Thanks,

Sajan.

 

[Sajan]

Hi,

I previously reported an approach using OFFSET to handle non-contiguous data for charts.


Here is an approach using the CHOOSE function so as to avoid having to use OFFSET:


Sample data:

[pre]

A	B	A	B	A	B	A	B	A	B
1	2	3	4	5	6	7	8	9	10

[/pre]
Desired chart: Plot only the "A" values (i.e. {1,3,5,7,9})


Assuming that the numbers above are in A2:J2, you could use the following formula:

=CHOOSE({1,2,1,2,1,2,1,2,1,2},$A$2:$J$2,NA())


This is a greatly simplified example, where I have used a constant array as the first argument to CHOOSE. You could just as easily create the first argument dynamically.


This takes advantage of the fact that Excel will not plot the #NA values. Using the CHOOSE function in this manner, you could substitute the positions in the range that you don't want with #NA.


Cheers,

-Sajan.