conditional data range for a chart series

[mhume]

Say I got a little table of data like so:

   A    B       C
1       Value   Flag
2  Jan  5       1
3  Feb  10      1
4  Mar  8       0
5  Apr  6       1
6  May  13      0
7  Jun  15      0
8  Jul  11      1
9  Aug  12      1
10 Sep  4       1
11 Nov  9       0
12 Dec  7       1

Say I want to make a simple bar chart with the "value" column as a series. The range for the value of that series is B2:B12. The name of that series is B1. The x-axis labels are A2:A12. Simple enough. Now, what if I just want a subset of the range where the corresponding "flag" value in that row is 1. In other words, I'd want to plot the following: B2,B3,B5,B8,B9,B10,B12.

Now, I can just seperate each cell ref by a comma to manually construct what I want, but there appears to be a limit to how many times I can do this, and it gets cumbersome if you had say 100 seperate cells to handle. It'd be better if there was a way to run through the range B2:B12, run an IF to check if the value in the C column is 1, and use that in the subset data range for the series.

I tried messing around with defines and the offset function, but I'm not looking for an offset range, I'm looking for a subset range.

Any help would be much appreciated.

-Matt

 

[Domenic]

Have you considered filtering the data on Column C for 1 and then creating a chart based on the filtered data?

 

[mhume]

Have you considered filtering the data on Column C for 1 and then creating a chart based on the filtered data?

Yes. It did cross my mind to create an "intermediate" table of filtered data that I could pull into the chart instead. I just thought their might have been a more graceful method. I just have a lot of sub-group charts I want to break the data into, and it felt excessive to duplicate data for them all.

The basic format of the data I'm working with is kind of like this: For every month from Jan07-Dec08, I have summary data for a group of people. Beneath that, I've split the summary data up for the specific "teams" that the people belong to, and beneath each team I've provided summary data for each individual. I used Excels outline feature to do that. So basically, when it's all collapsed you have a listing of the different months with the data. If you expand a month, it shows a breakdown summary of the different teams for that month, and if you expand a particular team, you get a breakdown of each individual on the team.

Say I wanted to add "average hours spent on projects" (one of the hypothetical data calculations) to the chart that would show me monthly data points for the entire group. If I specify the data range D3:D450, the chart looks great while the outline is collapsed, but it goes berserk when I expand everything.

Now, making intermediate, filtered data lists would work, but we're talking about 20+ lists and a lot of duplicate info. It just seems clumsy and I didn't want to clog the spreadsheet full of duplicate info that's only slightly reorganized. It would work though, I guess...

 

[Domenic]

First define (Insert > Name > Define) the following...

Num:

=COUNTIF(Sheet1!$C$2:$C$12,1)

Labels:

=T(OFFSET(Sheet1!$A$2:$A$12,SMALL(IF(Sheet1!$C$2:$C$12=1,ROW(Sheet1!$C$2:$C$12)-ROW(Sheet1!$C$2)),ROW(INDIRECT("Sheet1!1:"&Num))),0,1))

Note that I've assumed that the labels are text values. If, in fact, the labels are numerical values (true date values, formatted to display as "mmm"), change the T (in red) at the beginning of the reference to N.

Values:

=N(OFFSET(Sheet1!$B$2:$B$12,SMALL(IF(Sheet1!$C$2:$C$12=1,ROW(Sheet1!$C$2:$C$12)-ROW(Sheet1!$C$2)),ROW(INDIRECT("Sheet1!1:"&Num))),0,1))

Change the sheet references and adjust the ranges accordingly. Then define the source data for the series as follows...

Values:

='Workbook.xls'!Values

Category (X) axis labels:

='Workbook.xls'!Labels

Change the workbook reference accordingly.

Hope this helps!

 

[mhume]

Thanks a million for spending your time to help me out. I'll give your suggestion a try and see how it goes. I'll let you know how it works out. Thanks again.

 

[cwkemp]

I actually found this method of an intermediate table quite quick and straightforward so I thought I would just detail the solution I used as the alternative to the direct (but a bit more knowledge intensive version below).

I made two new columns on a new sheet:

A1 B1


where A1 =UNIQUE('Sheet1'!A$2:A$12)

and

where B1 =COUNTIFS('Sheet1'!AB$2:AB$12,A1,'Sheet1'!C$2:C$12,1) // You will need to drag this formula down across the # of rows of unique values


And then did a graph on the resulting data. This works well for me as I put the graph into the same sheet as these UNIQUE and COUNTIFS values.

Anyway the Named Defines work just as well, I just personally found they needed a bit more initial ramp up.