Subscribe
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
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
