turning lines on/off on a set of charts

  • Thread starter Thread starter Tuang
  • Start date Start date
T

Tuang

I have a set of columns and each contains a list of parameters that
describe a scenario. (Not any Excel-specific meaning of "scenario" (I
think there is such a thing), but just the ordinary meaning of "it
could be like this, or like this, or this...." parameter lists).

I may input the descriptions of ten different scenarios, and then I
have a set of charts for viewing the various implications of the
scenarios. Having ten lines on a single chart is way too confusing.
I'm usually interested in contrasting just two or three of them at a
time. If this were an ordinary app, I'd have a checkbox above each
column of parameters that would stand for "include in charts? yes or
no?".

I'm not quite sure how to do such a thing with Excel. I'm a programmer
(C++, C#, Lisp, etc.), but I'm a novice at Excel, so the simpler the
solution, the better. I realize that I can get the macro recorder to
write some useful code for me, and then I can try to tweak the code,
but I'm not even sure what I would do manually that I could record to
get code for adding and removing series from a set of charts (not just
one) in response to a "yes" or "no" in a cell.

I'd be grateful for any suggestions.

Thanks.
 
Tuang said:
I have a set of columns and each contains a list of parameters that
describe a scenario. (Not any Excel-specific meaning of "scenario" (I
think there is such a thing), but just the ordinary meaning of "it
could be like this, or like this, or this...." parameter lists).

I may input the descriptions of ten different scenarios, and then I
have a set of charts for viewing the various implications of the
scenarios. Having ten lines on a single chart is way too confusing.
I'm usually interested in contrasting just two or three of them at a
time. If this were an ordinary app, I'd have a checkbox above each
column of parameters that would stand for "include in charts? yes or
no?".
Click to expand...

I don't understand exactly what you're charting. But let me throw out a few
suggestions.

First, you could transpose your data. One row would be a set of parameters,
each row a different scenario. Then you could filter the data to exclude
any unwanted scenarios.

Second, keeping your existing data set, you could hide columns. You could
set the chart to not plot hidden cells, which would eliminate the unwanted
scenarios.

Finally, you might wish to consider a pivot chart. The pivot chart gives a
check-box list that allows you to select the data that appears on the chart.
I think that might be the slickest option. If you're unfamiliar with pivot
charts, go see this site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

Dave
dvt at psu dot edu
 
I don't understand exactly what you're charting. But let me throw out a few
suggestions.

First, you could transpose your data...
Click to expand...

No, no. I'm sorry I wasn't clear. I have a page (meaning worksheet)
of inputs. Imagine row1 being something like "NaCl concentration",
row2 being "starting temperature", row3 being "voltage", etc., and
each column contains a different set of these parameters. There's a
column with values for a scenario called "Option A", one to its right
called "Option B", etc., each column containing some value for each of
the input parameters.

Those inputs feed into several pages of models. The models do lots of
analysis and send their outputs to several summary worksheets. Imagine
one summary worksheet with the rows labeled Time 0, Time 1, Time 2...,
Time 1000. The columns show the output data for Option A, Option B,
.... Option Z. The cells show temperature, for example. Another summary
chart might show some other property at time 0 thru time 1000 --
chemical concentration or whatever.

Now I want to chart these outputs, but I have too many scenarios to
include them all on the charts simultaneously. Twenty lines ("series")
is too many for one chart. I might want to take a look at the
temperature curves of just Options A, D, E, and G on the charts. Then
I might want to remove G and add H and I. Then remove them all and
look at Options B vs. C.

What I'd like to do is just go to the input page and put either "yes"
or "no" at the top of each column to indicate whether I want the
charts to include the outputs from that scenario.

But I don't know how to create charts that dynamically add or remove
series depending on the value in some cell at the top of the input
columns on the input page.

I'd be grateful for any suggestions.
 
Back
Top