shading certain months on time chart

I have several years of data - the x-axis is a time-scale. I'd like to have
the winter months of the year shaded - to make it easier to see the
seasonality in the data. Is there a way to pick a period each year (e.g.
Nov. 15 - March 15) which will be shaded?

The easiest way to do this is to have a series of data for the winter months
only. That way you can shade that series differently.

"Bob Richardson" <bobr at whidbey dot com> wrote in message
news:[email protected]...
> I have several years of data - the x-axis is a time-scale. I'd like to
have
> the winter months of the year shaded - to make it easier to see the
> seasonality in the data. Is there a way to pick a period each year (e.g.
> Nov. 15 - March 15) which will be shaded?
>
>

Here's how I would do it. For one application see the 'Overview by
Day' page of the newsgroup stats (http://www.tushar-
mehta.com/excel/ngstats/overview-daily.html)

Suppose the months are in column A and the y-values in column B as in:
1-Jan 6
1-Feb 8
1-Mar 11
1-Apr 10
1-May 13
1-Jun 11
1-Jul 9
1-Aug 16
1-Sep 12
1-Oct 14
1-Nov 12
1-Dec 19
1-Jan 17
1-Feb 21
1-Mar 19
1-Apr 20
1-May 20
1-Jun 24
1-Jul 28
1-Aug 24
1-Sep 21
1-Oct 22
1-Nov 25
1-Dec 25

Then, in column C (C1 specifically) enter the formula
=IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())

Obviously, you would adjust the above formula for your specific needs.
This one 'shades' Nov.-Mar.
Copy C1 as far down col. C as there is data in column B.

Plot A:C as a column chart. Click the plotted series corresponding to
column B. Select Chart | Chart Type... and change it to a Line chart.

Double-click the plotted series corresponding to col. C. From the
Patterns tab, set the border to none and the area to some light color.
From the Options tab set the Gap Width to zero.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, "Bob Richardson"
<bobr at whidbey dot com> says...
> I have several years of data - the x-axis is a time-scale. I'd like to have
> the winter months of the year shaded - to make it easier to see the
> seasonality in the data. Is there a way to pick a period each year (e.g.
> Nov. 15 - March 15) which will be shaded?
>
>
>

If you want between November 15th and March 15th, you might need an equation
like this one.

=IF(OR(VALUE(MONTH(B1)&DAY(B1))>1115,VALUE(MONTH(B1)&DAY(B1))<315),B1,NA())

This is for data > 11/15 and < 3/15.

"Bob Richardson" <bobr at whidbey dot com> wrote in message
news:[email protected]...
> I have several years of data - the x-axis is a time-scale. I'd like to
have
> the winter months of the year shaded - to make it easier to see the
> seasonality in the data. Is there a way to pick a period each year (e.g.
> Nov. 15 - March 15) which will be shaded?
>
>

Tushar Mehta wrote:
> Here's how I would do it. For one application see the 'Overview by
> Day' page of the newsgroup stats (http://www.tushar-
> mehta.com/excel/ngstats/overview-daily.html)
>
> Suppose the months are in column A and the y-values in column B as in:
> 1-Jan 6
> 1-Feb 8
> 1-Mar 11
> 1-Apr 10
> 1-May 13
> 1-Jun 11
> 1-Jul 9
> 1-Aug 16
> 1-Sep 12
> 1-Oct 14
> 1-Nov 12
> 1-Dec 19
> 1-Jan 17
> 1-Feb 21
> 1-Mar 19
> 1-Apr 20
> 1-May 20
> 1-Jun 24
> 1-Jul 28
> 1-Aug 24
> 1-Sep 21
> 1-Oct 22
> 1-Nov 25
> 1-Dec 25
>
> Then, in column C (C1 specifically) enter the formula
> =IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
>
> Obviously, you would adjust the above formula for your specific needs.
> This one 'shades' Nov.-Mar.
> Copy C1 as far down col. C as there is data in column B.
>
> Plot A:C as a column chart. Click the plotted series corresponding to
> column B. Select Chart | Chart Type... and change it to a Line chart.
>
> Double-click the plotted series corresponding to col. C. From the
> Patterns tab, set the border to none and the area to some light color.
> From the Options tab set the Gap Width to zero.

Just a quick question here. Why isn't the logical test required here
an AND rather than OR?

John

If you choose>=November (11), you get Nov and Dec.
If you choose <=March (3), you get Jan, Feb, March.

If you select AND, you'll get nothing because there is nothing that is both
>=11 and <=3. That would work for dates between June and September,
however.

"John Cordes" <[email protected]> wrote in message
news:dNoQd.47781$gA4.17299@edtnps89...
> Tushar Mehta wrote:
> > Here's how I would do it. For one application see the 'Overview by
> > Day' page of the newsgroup stats (http://www.tushar-
> > mehta.com/excel/ngstats/overview-daily.html)
> >
> > Suppose the months are in column A and the y-values in column B as in:
> > 1-Jan 6
> > 1-Feb 8
> > 1-Mar 11
> > 1-Apr 10
> > 1-May 13
> > 1-Jun 11
> > 1-Jul 9
> > 1-Aug 16
> > 1-Sep 12
> > 1-Oct 14
> > 1-Nov 12
> > 1-Dec 19
> > 1-Jan 17
> > 1-Feb 21
> > 1-Mar 19
> > 1-Apr 20
> > 1-May 20
> > 1-Jun 24
> > 1-Jul 28
> > 1-Aug 24
> > 1-Sep 21
> > 1-Oct 22
> > 1-Nov 25
> > 1-Dec 25
> >
> > Then, in column C (C1 specifically) enter the formula
> > =IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
> >
> > Obviously, you would adjust the above formula for your specific needs.
> > This one 'shades' Nov.-Mar.
> > Copy C1 as far down col. C as there is data in column B.
> >
> > Plot A:C as a column chart. Click the plotted series corresponding to
> > column B. Select Chart | Chart Type... and change it to a Line chart.
> >
> > Double-click the plotted series corresponding to col. C. From the
> > Patterns tab, set the border to none and the area to some light color.
> > From the Options tab set the Gap Width to zero.
>
> Just a quick question here. Why isn't the logical test required here
> an AND rather than OR?
>
> John
>

In article <dNoQd.47781$gA4.17299@edtnps89>, [email protected] says...

> Just a quick question here. Why isn't the logical test required here
> an AND rather than OR?
>
Consider any month between Jan (1) and Dec (12). What will be the
result of the test Mth <=3 *and* Mth >=11?

Of course, if one reversed the test, the appropriate connector would be
AND. Hence, by the definition I used, non-Winter months would be
Mth >3 *and* Mth <11

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <dNoQd.47781$gA4.17299@edtnps89>, [email protected] says...
> Tushar Mehta wrote:
> > Here's how I would do it. For one application see the 'Overview by
> > Day' page of the newsgroup stats (http://www.tushar-
> > mehta.com/excel/ngstats/overview-daily.html)
> >
> > Suppose the months are in column A and the y-values in column B as in:
> > 1-Jan 6
> > 1-Feb 8
> > 1-Mar 11
> > 1-Apr 10
> > 1-May 13
> > 1-Jun 11
> > 1-Jul 9
> > 1-Aug 16
> > 1-Sep 12
> > 1-Oct 14
> > 1-Nov 12
> > 1-Dec 19
> > 1-Jan 17
> > 1-Feb 21
> > 1-Mar 19
> > 1-Apr 20
> > 1-May 20
> > 1-Jun 24
> > 1-Jul 28
> > 1-Aug 24
> > 1-Sep 21
> > 1-Oct 22
> > 1-Nov 25
> > 1-Dec 25
> >
> > Then, in column C (C1 specifically) enter the formula
> > =IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
> >
> > Obviously, you would adjust the above formula for your specific needs.
> > This one 'shades' Nov.-Mar.
> > Copy C1 as far down col. C as there is data in column B.
> >
> > Plot A:C as a column chart. Click the plotted series corresponding to
> > column B. Select Chart | Chart Type... and change it to a Line chart.
> >
> > Double-click the plotted series corresponding to col. C. From the
> > Patterns tab, set the border to none and the area to some light color.
> > From the Options tab set the Gap Width to zero.
>
> Just a quick question here. Why isn't the logical test required here
> an AND rather than OR?
>
> John
>
>

Tushar Mehta wrote:
> In article <dNoQd.47781$gA4.17299@edtnps89>, [email protected] says...
>
>
>> Just a quick question here. Why isn't the logical test required here
>>an AND rather than OR?
>>
>
> Consider any month between Jan (1) and Dec (12). What will be the
> result of the test Mth <=3 *and* Mth >=11?
>
> Of course, if one reversed the test, the appropriate connector would be
> AND. Hence, by the definition I used, non-Winter months would be
> Mth >3 *and* Mth <11
>
Sorry! Yes, I was using different inequalities, which required the AND,
and didn't look carefully enough at the original interval desired.

My apologies,
John

Nice idea Tushar - I've got the columnar chart looking pretty good. Is there
a way to control the width of the bars (e.g. set them all to "n" pixels
wide) while reducing the space between the columns to "y" pixels?

One more request. Is there a way to draw a horizontal line on the column
chart - to show the median?


"Tushar Mehta" <[email protected]> wrote in message
news:[email protected]...
> Here's how I would do it. For one application see the 'Overview by
> Day' page of the newsgroup stats (http://www.tushar-
> mehta.com/excel/ngstats/overview-daily.html)
>
> Suppose the months are in column A and the y-values in column B as in:
> 1-Jan 6
> 1-Feb 8
> 1-Mar 11
> 1-Apr 10
> 1-May 13
> 1-Jun 11
> 1-Jul 9
> 1-Aug 16
> 1-Sep 12
> 1-Oct 14
> 1-Nov 12
> 1-Dec 19
> 1-Jan 17
> 1-Feb 21
> 1-Mar 19
> 1-Apr 20
> 1-May 20
> 1-Jun 24
> 1-Jul 28
> 1-Aug 24
> 1-Sep 21
> 1-Oct 22
> 1-Nov 25
> 1-Dec 25
>
> Then, in column C (C1 specifically) enter the formula
> =IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
>
> Obviously, you would adjust the above formula for your specific needs.
> This one 'shades' Nov.-Mar.
> Copy C1 as far down col. C as there is data in column B.
>
> Plot A:C as a column chart. Click the plotted series corresponding to
> column B. Select Chart | Chart Type... and change it to a Line chart.
>
> Double-click the plotted series corresponding to col. C. From the
> Patterns tab, set the border to none and the area to some light color.
> From the Options tab set the Gap Width to zero.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>, "Bob Richardson"
> <bobr at whidbey dot com> says...
>> I have several years of data - the x-axis is a time-scale. I'd like to
>> have
>> the winter months of the year shaded - to make it easier to see the
>> seasonality in the data. Is there a way to pick a period each year (e.g.
>> Nov. 15 - March 15) which will be shaded?
>>
>>
>>

1. Double click on the columns, and on the Options tab, set Gap Width to
zero.

2. Horizontal line:

http://peltiertech.com/Excel/Charts/AddLine.html

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

Bob Richardson wrote:

> Nice idea Tushar - I've got the columnar chart looking pretty good. Is there
> a way to control the width of the bars (e.g. set them all to "n" pixels
> wide) while reducing the space between the columns to "y" pixels?
>
> One more request. Is there a way to draw a horizontal line on the column
> chart - to show the median?
>
>
> "Tushar Mehta" <[email protected]> wrote in message
> news:[email protected]...
>
>>Here's how I would do it. For one application see the 'Overview by
>>Day' page of the newsgroup stats (http://www.tushar-
>>mehta.com/excel/ngstats/overview-daily.html)
>>
>>Suppose the months are in column A and the y-values in column B as in:
>>1-Jan 6
>>1-Feb 8
>>1-Mar 11
>>1-Apr 10
>>1-May 13
>>1-Jun 11
>>1-Jul 9
>>1-Aug 16
>>1-Sep 12
>>1-Oct 14
>>1-Nov 12
>>1-Dec 19
>>1-Jan 17
>>1-Feb 21
>>1-Mar 19
>>1-Apr 20
>>1-May 20
>>1-Jun 24
>>1-Jul 28
>>1-Aug 24
>>1-Sep 21
>>1-Oct 22
>>1-Nov 25
>>1-Dec 25
>>
>>Then, in column C (C1 specifically) enter the formula
>>=IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
>>
>>Obviously, you would adjust the above formula for your specific needs.
>>This one 'shades' Nov.-Mar.
>>Copy C1 as far down col. C as there is data in column B.
>>
>>Plot A:C as a column chart. Click the plotted series corresponding to
>>column B. Select Chart | Chart Type... and change it to a Line chart.
>>
>>Double-click the plotted series corresponding to col. C. From the
>>Patterns tab, set the border to none and the area to some light color.
>>From the Options tab set the Gap Width to zero.
>>
>>--
>>Regards,
>>
>>Tushar Mehta
>>www.tushar-mehta.com
>>Excel, PowerPoint, and VBA add-ins, tutorials
>>Custom MS Office productivity solutions
>>
>>In article <[email protected]>, "Bob Richardson"
>><bobr at whidbey dot com> says...
>>
>>>I have several years of data - the x-axis is a time-scale. I'd like to
>>>have
>>>the winter months of the year shaded - to make it easier to see the
>>>seasonality in the data. Is there a way to pick a period each year (e.g.
>>>Nov. 15 - March 15) which will be shaded?
>>>
>>>
>>>
>
>
>

Thanks Jon,

I followed you all the way to the last step....then tripped

How did you get the average to appear in the secondary y-axis?
Do you take the value from the calculated field (in your little average
table) or did you "cheat" and just type it in the title of the y-axis?

How can you get that label to appear right at the height of the line.

The only way I could get a label there was to type in it, and it's displayed
in the middle of the y-axis, not right at the average.

Bob

"Jon Peltier" <[email protected]> wrote in message
news:[email protected]...
> 1. Double click on the columns, and on the Options tab, set Gap Width to
> zero.
>
> 2. Horizontal line:
>
> http://peltiertech.com/Excel/Charts/AddLine.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Bob Richardson wrote:
>
>> Nice idea Tushar - I've got the columnar chart looking pretty good. Is
>> there a way to control the width of the bars (e.g. set them all to "n"
>> pixels wide) while reducing the space between the columns to "y" pixels?
>>
>> One more request. Is there a way to draw a horizontal line on the column
>> chart - to show the median?
>>
>>
>> "Tushar Mehta" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Here's how I would do it. For one application see the 'Overview by
>>>Day' page of the newsgroup stats (http://www.tushar-
>>>mehta.com/excel/ngstats/overview-daily.html)
>>>
>>>Suppose the months are in column A and the y-values in column B as in:
>>>1-Jan 6
>>>1-Feb 8
>>>1-Mar 11
>>>1-Apr 10
>>>1-May 13
>>>1-Jun 11
>>>1-Jul 9
>>>1-Aug 16
>>>1-Sep 12
>>>1-Oct 14
>>>1-Nov 12
>>>1-Dec 19
>>>1-Jan 17
>>>1-Feb 21
>>>1-Mar 19
>>>1-Apr 20
>>>1-May 20
>>>1-Jun 24
>>>1-Jul 28
>>>1-Aug 24
>>>1-Sep 21
>>>1-Oct 22
>>>1-Nov 25
>>>1-Dec 25
>>>
>>>Then, in column C (C1 specifically) enter the formula
>>>=IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
>>>
>>>Obviously, you would adjust the above formula for your specific needs.
>>>This one 'shades' Nov.-Mar.
>>>Copy C1 as far down col. C as there is data in column B.
>>>
>>>Plot A:C as a column chart. Click the plotted series corresponding to
>>>column B. Select Chart | Chart Type... and change it to a Line chart.
>>>
>>>Double-click the plotted series corresponding to col. C. From the
>>>Patterns tab, set the border to none and the area to some light color.
>>>From the Options tab set the Gap Width to zero.
>>>
>>>--
>>>Regards,
>>>
>>>Tushar Mehta
>>>www.tushar-mehta.com
>>>Excel, PowerPoint, and VBA add-ins, tutorials
>>>Custom MS Office productivity solutions
>>>
>>>In article <[email protected]>, "Bob Richardson"
>>><bobr at whidbey dot com> says...
>>>
>>>>I have several years of data - the x-axis is a time-scale. I'd like to
>>>>have
>>>>the winter months of the year shaded - to make it easier to see the
>>>>seasonality in the data. Is there a way to pick a period each year (e.g.
>>>>Nov. 15 - March 15) which will be shaded?
>>>>
>>>>
>>>>
>>
>>

You may also want to check
Straight lines
http://www.tushar-mehta.com/excel/ch...nes/index.html

It has its advantages (and disadvantages) over the method Jon describes
on his web site.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, "Bob Richardson"
<bobr at whidbey dot com> says...
> Thanks Jon,
>
> I followed you all the way to the last step....then tripped
>
> How did you get the average to appear in the secondary y-axis?
> Do you take the value from the calculated field (in your little average
> table) or did you "cheat" and just type it in the title of the y-axis?
>
> How can you get that label to appear right at the height of the line.
>
> The only way I could get a label there was to type in it, and it's displayed
> in the middle of the y-axis, not right at the average.
>
> Bob
>
> "Jon Peltier" <[email protected]> wrote in message
> news:[email protected]...
> > 1. Double click on the columns, and on the Options tab, set Gap Width to
> > zero.
> >
> > 2. Horizontal line:
> >
> > http://peltiertech.com/Excel/Charts/AddLine.html
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> >
> > Bob Richardson wrote:
> >
> >> Nice idea Tushar - I've got the columnar chart looking pretty good. Is
> >> there a way to control the width of the bars (e.g. set them all to "n"
> >> pixels wide) while reducing the space between the columns to "y" pixels?
> >>
> >> One more request. Is there a way to draw a horizontal line on the column
> >> chart - to show the median?
> >>
> >>
> >> "Tushar Mehta" <[email protected]> wrote in message
> >> news:[email protected]...
> >>
> >>>Here's how I would do it. For one application see the 'Overview by
> >>>Day' page of the newsgroup stats (http://www.tushar-
> >>>mehta.com/excel/ngstats/overview-daily.html)
> >>>
> >>>Suppose the months are in column A and the y-values in column B as in:
> >>>1-Jan 6
> >>>1-Feb 8
> >>>1-Mar 11
> >>>1-Apr 10
> >>>1-May 13
> >>>1-Jun 11
> >>>1-Jul 9
> >>>1-Aug 16
> >>>1-Sep 12
> >>>1-Oct 14
> >>>1-Nov 12
> >>>1-Dec 19
> >>>1-Jan 17
> >>>1-Feb 21
> >>>1-Mar 19
> >>>1-Apr 20
> >>>1-May 20
> >>>1-Jun 24
> >>>1-Jul 28
> >>>1-Aug 24
> >>>1-Sep 21
> >>>1-Oct 22
> >>>1-Nov 25
> >>>1-Dec 25
> >>>
> >>>Then, in column C (C1 specifically) enter the formula
> >>>=IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
> >>>
> >>>Obviously, you would adjust the above formula for your specific needs.
> >>>This one 'shades' Nov.-Mar.
> >>>Copy C1 as far down col. C as there is data in column B.
> >>>
> >>>Plot A:C as a column chart. Click the plotted series corresponding to
> >>>column B. Select Chart | Chart Type... and change it to a Line chart.
> >>>
> >>>Double-click the plotted series corresponding to col. C. From the
> >>>Patterns tab, set the border to none and the area to some light color.
> >>>From the Options tab set the Gap Width to zero.
> >>>
> >>>--
> >>>Regards,
> >>>
> >>>Tushar Mehta
> >>>www.tushar-mehta.com
> >>>Excel, PowerPoint, and VBA add-ins, tutorials
> >>>Custom MS Office productivity solutions
> >>>
> >>>In article <[email protected]>, "Bob Richardson"
> >>><bobr at whidbey dot com> says...
> >>>
> >>>>I have several years of data - the x-axis is a time-scale. I'd like to
> >>>>have
> >>>>the winter months of the year shaded - to make it easier to see the
> >>>>seasonality in the data. Is there a way to pick a period each year (e.g.
> >>>>Nov. 15 - March 15) which will be shaded?
> >>>>
> >>>>
> >>>>
> >>
> >>
>
>

Bob -

That label is not on the secondary axis. It is a data label (Show
Values) for the point plotted at the average value along the right edge
of the chart.

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

Bob Richardson wrote:
> Thanks Jon,
>
> I followed you all the way to the last step....then tripped
>
> How did you get the average to appear in the secondary y-axis?
> Do you take the value from the calculated field (in your little average
> table) or did you "cheat" and just type it in the title of the y-axis?
>
> How can you get that label to appear right at the height of the line.
>
> The only way I could get a label there was to type in it, and it's displayed
> in the middle of the y-axis, not right at the average.
>
> Bob
>
> "Jon Peltier" <[email protected]> wrote in message
> news:[email protected]...
>
>>1. Double click on the columns, and on the Options tab, set Gap Width to
>>zero.
>>
>>2. Horizontal line:
>>
>> http://peltiertech.com/Excel/Charts/AddLine.html
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Bob Richardson wrote:
>>
>>
>>>Nice idea Tushar - I've got the columnar chart looking pretty good. Is
>>>there a way to control the width of the bars (e.g. set them all to "n"
>>>pixels wide) while reducing the space between the columns to "y" pixels?
>>>
>>>One more request. Is there a way to draw a horizontal line on the column
>>>chart - to show the median?
>>>
>>>
>>>"Tushar Mehta" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>
>>>>Here's how I would do it. For one application see the 'Overview by
>>>>Day' page of the newsgroup stats (http://www.tushar-
>>>>mehta.com/excel/ngstats/overview-daily.html)
>>>>
>>>>Suppose the months are in column A and the y-values in column B as in:
>>>>1-Jan 6
>>>>1-Feb 8
>>>>1-Mar 11
>>>>1-Apr 10
>>>>1-May 13
>>>>1-Jun 11
>>>>1-Jul 9
>>>>1-Aug 16
>>>>1-Sep 12
>>>>1-Oct 14
>>>>1-Nov 12
>>>>1-Dec 19
>>>>1-Jan 17
>>>>1-Feb 21
>>>>1-Mar 19
>>>>1-Apr 20
>>>>1-May 20
>>>>1-Jun 24
>>>>1-Jul 28
>>>>1-Aug 24
>>>>1-Sep 21
>>>>1-Oct 22
>>>>1-Nov 25
>>>>1-Dec 25
>>>>
>>>>Then, in column C (C1 specifically) enter the formula
>>>>=IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
>>>>
>>>>Obviously, you would adjust the above formula for your specific needs.
>>>>This one 'shades' Nov.-Mar.
>>>>Copy C1 as far down col. C as there is data in column B.
>>>>
>>>>Plot A:C as a column chart. Click the plotted series corresponding to
>>>>column B. Select Chart | Chart Type... and change it to a Line chart.
>>>>
>>>>Double-click the plotted series corresponding to col. C. From the
>>>>Patterns tab, set the border to none and the area to some light color.
>>>
>>>>From the Options tab set the Gap Width to zero.
>>>
>>>>--
>>>>Regards,
>>>>
>>>>Tushar Mehta
>>>>www.tushar-mehta.com
>>>>Excel, PowerPoint, and VBA add-ins, tutorials
>>>>Custom MS Office productivity solutions
>>>>
>>>>In article <[email protected]>, "Bob Richardson"
>>>><bobr at whidbey dot com> says...
>>>>
>>>>
>>>>>I have several years of data - the x-axis is a time-scale. I'd like to
>>>>>have
>>>>>the winter months of the year shaded - to make it easier to see the
>>>>>seasonality in the data. Is there a way to pick a period each year (e.g.
>>>>>Nov. 15 - March 15) which will be shaded?
>>>>>
>>>>>
>>>>>
>>>
>>>
>

Got it It wasn't obvious to me at first to right click on the little
spot where the average bar crosses the y axis on the right side.


"Jon Peltier" <[email protected]> wrote in message
news:[email protected]...
> Bob -
>
> That label is not on the secondary axis. It is a data label (Show Values)
> for the point plotted at the average value along the right edge of the
> chart.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Bob Richardson wrote:
>> Thanks Jon,
>>
>> I followed you all the way to the last step....then tripped
>>
>> How did you get the average to appear in the secondary y-axis?
>> Do you take the value from the calculated field (in your little average
>> table) or did you "cheat" and just type it in the title of the y-axis?
>>
>> How can you get that label to appear right at the height of the line.
>>
>> The only way I could get a label there was to type in it, and it's
>> displayed in the middle of the y-axis, not right at the average.
>>
>> Bob
>>
>> "Jon Peltier" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>1. Double click on the columns, and on the Options tab, set Gap Width to
>>>zero.
>>>
>>>2. Horizontal line:
>>>
>>> http://peltiertech.com/Excel/Charts/AddLine.html
>>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>Bob Richardson wrote:
>>>
>>>
>>>>Nice idea Tushar - I've got the columnar chart looking pretty good. Is
>>>>there a way to control the width of the bars (e.g. set them all to "n"
>>>>pixels wide) while reducing the space between the columns to "y" pixels?
>>>>
>>>>One more request. Is there a way to draw a horizontal line on the column
>>>>chart - to show the median?
>>>>
>>>>
>>>>"Tushar Mehta" <[email protected]> wrote in
>>>>message news:[email protected]...
>>>>
>>>>
>>>>>Here's how I would do it. For one application see the 'Overview by
>>>>>Day' page of the newsgroup stats (http://www.tushar-
>>>>>mehta.com/excel/ngstats/overview-daily.html)
>>>>>
>>>>>Suppose the months are in column A and the y-values in column B as in:
>>>>>1-Jan 6
>>>>>1-Feb 8
>>>>>1-Mar 11
>>>>>1-Apr 10
>>>>>1-May 13
>>>>>1-Jun 11
>>>>>1-Jul 9
>>>>>1-Aug 16
>>>>>1-Sep 12
>>>>>1-Oct 14
>>>>>1-Nov 12
>>>>>1-Dec 19
>>>>>1-Jan 17
>>>>>1-Feb 21
>>>>>1-Mar 19
>>>>>1-Apr 20
>>>>>1-May 20
>>>>>1-Jun 24
>>>>>1-Jul 28
>>>>>1-Aug 24
>>>>>1-Sep 21
>>>>>1-Oct 22
>>>>>1-Nov 25
>>>>>1-Dec 25
>>>>>
>>>>>Then, in column C (C1 specifically) enter the formula
>>>>>=IF(OR(MONTH(A1)<=3,MONTH(A1)>=11),MAX($B$1:$B$24),NA())
>>>>>
>>>>>Obviously, you would adjust the above formula for your specific needs.
>>>>>This one 'shades' Nov.-Mar.
>>>>>Copy C1 as far down col. C as there is data in column B.
>>>>>
>>>>>Plot A:C as a column chart. Click the plotted series corresponding to
>>>>>column B. Select Chart | Chart Type... and change it to a Line chart.
>>>>>
>>>>>Double-click the plotted series corresponding to col. C. From the
>>>>>Patterns tab, set the border to none and the area to some light color.
>>>>
>>>>>From the Options tab set the Gap Width to zero.
>>>>
>>>>>--
>>>>>Regards,
>>>>>
>>>>>Tushar Mehta
>>>>>www.tushar-mehta.com
>>>>>Excel, PowerPoint, and VBA add-ins, tutorials
>>>>>Custom MS Office productivity solutions
>>>>>
>>>>>In article <[email protected]>, "Bob Richardson"
>>>>><bobr at whidbey dot com> says...
>>>>>
>>>>>
>>>>>>I have several years of data - the x-axis is a time-scale. I'd like to
>>>>>>have
>>>>>>the winter months of the year shaded - to make it easier to see the
>>>>>>seasonality in the data. Is there a way to pick a period each year
>>>>>>(e.g.
>>>>>>Nov. 15 - March 15) which will be shaded?
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>