line chart with separated X data

  • Thread starter Thread starter Robert H
  • Start date Start date
R

Robert H

I need to create a main effects chart which plots three factors (x
data) against two levels (y data).
example http://www.itl.nist.gov/div898/handbook/pri/section5/gifs/dexmean.gif
This is simple enough using a line chart however I need to separate
the factors from each other, have them sit side by side rather than on
top of each other. So far the only way I have found to do this is to
make three charts, one effect each. One chart with a large chart area
and a reduced plot area. The other two charts have the chat area
hidden (border and fill off) and lay over the first chart so that the
plot areas are aligned side by side. This prototype is way too
cumbersome but I'm having a hard time figuring out how to do this in a
single chart.
Any help will be appreciated.
Thank
Robert
 
Thanks Jon, This is an easy to understand method to work around the
limitations of Excel or should I say work within the framework of
Excel. I dont like having to duplicate the data in a separate table
but sometimes thats what you have to do...
Thanks Again
Robert
 
That took me a little bit to figure out, your rework of my chart realy
helped. Labels are actually series data, and the separators are error
bars. Clever!
I'm adding a table for the Grand Mean that crosses the entire chart
and naming the source data to feed the chart page data table.

Later I might setup the tables to grow accordingy if I add factors but
I may find that I dont change it that much...
Thanks
Robert
 
Re duplication of data in a separate table...

Worksheet space is cheap. Formulas that retain linkage to the original data
are easy. Formulas that set up the data range based on conditions[1] are
easy.

Time spent trying to do too many things with one set of data (monitor
display, B&W printout display, chart source, calculation source, different
reports for different audiences) is hard, the mental effort is tiring, the
circles you run in get you nowhere.

Bottom line: use the extra space for another data range. For every fine
minutes you spend with your data, you save five hours of aggravation. (I'm
thinking of trademarking that phrase.)

[1] http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


Thanks Jon, This is an easy to understand method to work around the
limitations of Excel or should I say work within the framework of
Excel. I dont like having to duplicate the data in a separate table
but sometimes thats what you have to do...
Thanks Again
Robert
 
Perfect delivery Jon. It sounds like you are speaking from
experience. Requardless, what you said is causing me to rethink how I
do things. Your timing could not have been better.
Robert

Re duplication of data in a separate table...

Worksheet space is cheap. Formulas that retain linkage to the original data
are easy. Formulas that set up the data range based on conditions[1] are
easy.

Time spent trying to do too many things with one set of data (monitor
display, B&W printout display, chart source, calculation source, different
reports for different audiences) is hard, the mental effort is tiring, the
circles you run in get you nowhere.

Bottom line: use the extra space for another data range. For every fine
minutes you spend with your data, you save five hours of aggravation. (I'm
thinking of trademarking that phrase.)

[1]http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______


Thanks Jon, This is an easy to understand method to work around the
limitations of Excel or should I say work within the framework of
Excel. I dont like having to duplicate the data in a separate table
but sometimes thats what you have to do...
Thanks Again
Robert

Robert -
Click to expand...
Rather than type out an all-text answer, I wrote an illustrated blog post
to
show just how easy this chart type is to create in Excel. It's a single
simple line chart, and all the hard work is done through careful
arrangement
of the data. The blog post is located here:

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Lots of experience. I follow the KISYM principle (Keep It Simple, You
Moron!), aka the KISS principle. Keeping it simple does not mean using fewer
sheets or smaller ranges. KISS does not mean writing wildly complex formulas
to spare the use of a helper column. KISS means use all the extra ranges and
sheets I need, because in six months or six years when I return to that
intricately nested formula, it would take me six hours to figure out,
whereas the extra ranges is nearly self evident, especially if I throw
around a few labels.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


Perfect delivery Jon. It sounds like you are speaking from
experience. Requardless, what you said is causing me to rethink how I
do things. Your timing could not have been better.
Robert

Re duplication of data in a separate table...

Worksheet space is cheap. Formulas that retain linkage to the original
data
are easy. Formulas that set up the data range based on conditions[1] are
easy.

Time spent trying to do too many things with one set of data (monitor
display, B&W printout display, chart source, calculation source, different
reports for different audiences) is hard, the mental effort is tiring, the
circles you run in get you nowhere.

Bottom line: use the extra space for another data range. For every fine
minutes you spend with your data, you save five hours of aggravation. (I'm
thinking of trademarking that phrase.)

[1]http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______


Thanks Jon, This is an easy to understand method to work around the
limitations of Excel or should I say work within the framework of
Excel. I dont like having to duplicate the data in a separate table
but sometimes thats what you have to do...
Thanks Again
Robert

Robert -
Click to expand...
Rather than type out an all-text answer, I wrote an illustrated blog
post
to
show just how easy this chart type is to create in Excel. It's a single
simple line chart, and all the hard work is done through careful
arrangement
of the data. The blog post is located here:

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top