line chart with separated X data

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/hand...ifs/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

Robert:

You want to make a horizontal panel chart. Here's a link to an Excel how-to.

http://processtrends.com/pg_charts_...el_XY_chart.htm

If you run into trouble, send me the file and I'll try to make it for you.

Kelly

edit address and send to:

koday*at*processtrends*dot*com



"Robert H" <robert.hatcher@l-3com.com> wrote in message
news:b1aa597f-0232-4250-884f-bb68f3d8f442@o77g2000hsf.googlegroups.com...
>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/hand...ifs/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

Robert -

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:

http://peltiertech.com/WordPress/20...n-effects-plot/

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


"Robert H" <robert.hatcher@l-3com.com> wrote in message
news:b1aa597f-0232-4250-884f-bb68f3d8f442@o77g2000hsf.googlegroups.com...
>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/hand...ifs/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

On Mar 10, 8:45*pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Robert -
>
> 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:
>
> http://peltiertech.com/WordPress/20...-main-effects-p...
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> news:b1aa597f-0232-4250-884f-bb68f3d8f442@o77g2000hsf.googlegroups.com...
>
>
>
> >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/hand...ifs/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- Hide quoted text -
>
> - Show quoted text -

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


On Mar 10, 8:04*pm, "Kelly O'Day" <d...@comcast.net> wrote:
> Robert:
>
> You want to make a horizontal panel chart. Here's a link to an Excel how-to.
>
> http://processtrends.com/pg_charts_...el_XY_chart.htm
>
> If you run into trouble, send me the file and I'll try to make it for you.
>
> Kelly
>
> edit address and send to:
>
> koday*at*processtrends*dot*com
>
> "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> news:b1aa597f-0232-4250-884f-bb68f3d8f442@o77g2000hsf.googlegroups.com...
>
>
>
> >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/hand...ifs/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- Hide quoted text -
>
> - Show quoted text -

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...onalChart1.html

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


"Robert H" <robert.hatcher@l-3com.com> wrote in message
news:724fff0a-08b2-4427-b8b3-34cc612f9cd9@i29g2000prf.googlegroups.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

On Mar 10, 8:45 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Robert -
>
> 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:
>
> http://peltiertech.com/WordPress/20...-main-effects-p...
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> news:b1aa597f-0232-4250-884f-bb68f3d8f442@o77g2000hsf.googlegroups.com...
>
>
>
> >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/hand...ifs/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- Hide quoted text -
>
> - Show quoted text -

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

On Mar 12, 8:42*am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> 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
> _______
>
> "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> news:724fff0a-08b2-4427-b8b3-34cc612f9cd9@i29g2000prf.googlegroups.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
>
> On Mar 10, 8:45 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
> > Robert -
>
> > 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:
>
> >http://peltiertech.com/WordPress/20...-main-effects-p...
>
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. -http://PeltierTech.com
> > _______
>
> > "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> >news:b1aa597f-0232-4250-884f-bb68f3d8f442@o77g2000hsf.googlegroups.com...
>
> > >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/hand...ifs/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- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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
_______


"Robert H" <robert.hatcher@l-3com.com> wrote in message
news:9e122a47-2c50-4d74-a435-63c65abe2773@x41g2000hsb.googlegroups.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

On Mar 12, 8:42 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> 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
> _______
>
> "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> news:724fff0a-08b2-4427-b8b3-34cc612f9cd9@i29g2000prf.googlegroups.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
>
> On Mar 10, 8:45 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
> > Robert -
>
> > 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:
>
> >http://peltiertech.com/WordPress/20...-main-effects-p...
>
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. -http://PeltierTech.com
> > _______
>
> > "Robert H" <robert.hatc...@l-3com.com> wrote in message
>
> >news:b1aa597f-0232-4250-884f-bb68f3d8f442@o77g2000hsf.googlegroups.com...
>
> > >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/hand...ifs/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- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -