Dynamically generate contiguous data in array for chart from non-contiguos range

[JohnF123]

I'm trying to create a dynamic range of contiguous data in an array from a non-contiguous range of cells that I can use in a named range for a chart.


The problem I'm having is removing 0's from my array so I'm only left with the relevant data I want to use in the chart.


For example in the table below I want to chart from column C (value) only the days with 1 in column B (Weekday.) The resulting data is 22,34,15 which come from cells C2, C9 and C16. When I use the following formula =($B$2:$B$22=1)*($C$2:$C$22)I get those numbers but I also get zero fillers in between that I don't want in my chart like so {22;0;0;0;0;0;0;34;0;0;0;0;0;0;15;0;0;0;0;0;0}


I've replaced the zeros with #NA, and the values are not included in the chart, but the tick marks for those values are in the bottom X-Axes of the chart creating a chart much larger than I want.


How can I create an array formula to include only the data to be charted and not the zeros?


Thank you for your help

[pre]

Row #    Col A        Col B   Col C
1	Date	      Weekday  Value
2	10/1/2012	1	22
3	10/2/2012	2	25
4	10/3/2012	3	18
5	10/4/2012	4	36
6	10/5/2012	5	50
7	10/6/2012	6	10
8	10/7/2012	7	8
9	10/8/2012	1	34
10	10/9/2012	2	41
11	10/10/2012	3	36
12	10/11/2012	4	31
13	10/12/2012	5	25
14	10/13/2012	6	22
15	10/14/2012	7	45
16	10/15/2012	1	15
17	10/16/2012	2	17
18	10/17/2012	3	10
19	10/18/2012	4	19
20	10/19/2012	5	29
21	10/20/2012	6	39
22	10/21/2012	7	41

[/pre]

 

[NARAYANK991]

Hi ,


A similar post is available here :


http://chandoo.org/forums/topic/dynamic-charts-with-non-contiguous-data


If you have any difficulty in using those techniques to solve your problem , see if this is acceptable :


http://speedy.sh/K3jrw/Book12.xlsx


Narayan

 

[JohnF123]

Thanks Narayan, I've seen that post and studied and tried all the options recommended, but they either rely on helper columns (which is not an option for me to use), or they used the small function in an array formula which sorted the data in ascending order which is also not what I want, or they used #N/A to replace the zeros which creates the problem I described in my original post.

 

[NARAYANK991]

Hi John ,


I think not using any helper columns makes it very difficult. I'll post back if something comes up.


Narayan

 

[Sajan]

Hi John,


There are many ways you can eliminate zeros in an array, while retaining the sort order of the array. The following is one approach:

=ROUND(MOD(SMALL(IF(WeekDayList=1, ROW(ValueList) + ValueList%%), ROW($A$1:INDEX($A:$A, COUNTIF(WeekDayList,1)))),1)*10^4,0)


If you setup a "Name" with the above formula, you should be able to reference it in your chart, and dynamically show only the values corresponding to weekday=1.


You would only need helper columns if you wish to show some text values (e.g. department name) corresponding to the condition weekday=1 as the X-Axis. Based on your sample data, you would not need any helper columns.


Cheers,

Sajan.

 

[JohnF123]

Wahoooo!!!! That worked Sajan! Thank you so much!!!


Now I have to study this and figure out how it works....I love this stuff!

 

[Sajan]

Hi John,

Thanks for the feedback! Glad to help!


After you study it, please come back with any questions or clarifications.


Cheers,

Sajan.

 

[JohnF123]

Very creative use of the small function to combine the row number as the whole number and the value in that row as the decimal portion (ROW(ValueList) + ValueList%%) so when the small function sorts the data in the array in ascending order it is sorting by row which happens to have the value attached to it. It's so simple it makes me wonder how I overlooked the solution in the first place. Thanks again Sajan!

 

[Sajan]

Hi John,

I have often overlooked many simple ideas myself! That is why this community is very helpful!


Cheers,

Sajan.

 

[NARAYANK991]

Hi Sajan ,


I think you are being very modest ; the concept of using the actual value as a decimal suffix to the row ( so as to ensure that the SMALL function will not rearrange the data ) , using MOD to eliminate the integer portion , and then using the 10^4 operation to bring back the original value ; the entire combination is the product of an Excel master ! By no stretch of imagination can you call this simple.


Incidentally , the ROUND(....,0) should ideally be changed so that instead of 0 as the second parameter , we have the maximum number of decimal places present in the original input ; if the original input has decimal numbers , 0 as the second parameter will not give the correct results.


Narayan

 

[JohnF123]

Good point about the rounding Narayan. My data only has whole numbers which are being used in a chart and I don't need to have fractional accuracy, but future data sets may be used for other purposes and contain fractions, so the rounding is something to keep an eye on.


Thank you both so much for all your help!!!

John

 

[Sajan]

Hi Narayan,

You are very kind! Thank you!


Great point about the rounding of fractions.


Regards,

Sajan.

 

[Hui]

You may want to read Sajan's excellent post which describes how this technique works:

http://chandoo.org/wp/2012/12/04/formula-forensics-no-032-creating-dynamic-charts-with-non-contiguous-data/