vertical line in area charts
 

howdy

ive got a chart which gives production values by year for different
countries. the problem is one country starts production in the middle
of the data range and so excel draws the line from zero at the year
before, so it rises slowly.
i want the graph to jump straight to its value at the year production
commences; how can i do this?

(sorry if this is unclear, i would add a picture but i dont have
hosting or anything!)

thanks!

vertical line in area charts
 

Hi,

If the line is rising from zero it would suggest that the periods before
real values contain a value or formula that the chart is treating as
zero. Either make sure the cells are really empty or your formula uses
NA() for non values.

Note this will not a vertical jump in the line rather the line will
simply being at the Y value for that period. If you want a vertical line
you will need to include an extra data point that has the period and a
zero value. You then need to ensure the x axis is being plotted as true
values and not categories other wise a double set of data for that
period will be shown.
Another way to get the dropline to zero would be to use custom Y Error
bars, where only that period has a drop of the value for that period.

Cheers
Andy

wrote:
howdy

ive got a chart which gives production values by year for different
countries. the problem is one country starts production in the middle
of the data range and so excel draws the line from zero at the year
before, so it rises slowly.
i want the graph to jump straight to its value at the year production
commences; how can i do this?

(sorry if this is unclear, i would add a picture but i dont have
hosting or anything!)

thanks!


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

vertical line in area charts
 

Howdy again

Thanks for the reply.

From what you say, I should have blank cells before the start of
production? Well, I do but still it slopes down. I really need the
vertical jump for this graph to make sense!

Just for information here is a little bit of the data just to show you
waht i mean

TIME .... 1983 1984 1985 1986
Russia - - 4830 5260
FSU 5640 6150 - -
Rest of FSU - - 1900 1920

So ideally the vertical line comes down in 1984 and the components
continue on thereafter.

Andy Pope wrote:

Hi,

If the line is rising from zero it would suggest that the periods before
real values contain a value or formula that the chart is treating as
zero. Either make sure the cells are really empty or your formula uses
NA() for non values.

Note this will not a vertical jump in the line rather the line will
simply being at the Y value for that period. If you want a vertical line
you will need to include an extra data point that has the period and a
zero value. You then need to ensure the x axis is being plotted as true
values and not categories other wise a double set of data for that
period will be shown.
Another way to get the dropline to zero would be to use custom Y Error
bars, where only that period has a drop of the value for that period.

vertical line in area charts
 

Are those - place holders for posting purposes or what you currently
have in the cells?

wrote:
Howdy again

Thanks for the reply.

From what you say, I should have blank cells before the start of
production? Well, I do but still it slopes down. I really need the
vertical jump for this graph to make sense!

Just for information here is a little bit of the data just to show you
waht i mean

TIME .... 1983 1984 1985 1986
Russia - - 4830 5260
FSU 5640 6150 - -
Rest of FSU - - 1900 1920

So ideally the vertical line comes down in 1984 and the components
continue on thereafter.

Andy Pope wrote:


Hi,

If the line is rising from zero it would suggest that the periods before
real values contain a value or formula that the chart is treating as
zero. Either make sure the cells are really empty or your formula uses
NA() for non values.

Note this will not a vertical jump in the line rather the line will
simply being at the Y value for that period. If you want a vertical line
you will need to include an extra data point that has the period and a
zero value. You then need to ensure the x axis is being plotted as true
values and not categories other wise a double set of data for that
period will be shown.
Another way to get the dropline to zero would be to use custom Y Error
bars, where only that period has a drop of the value for that period.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

vertical line in area charts
 

sorry i meant to say, '-' indicates a blank cell. the figures are just
rough but close enough (everything I have in the cells are values; no
formulas at all).

Andy Pope wrote:

Are those - place holders for posting purposes or what you currently
have in the cells?

wrote:
Howdy again

Thanks for the reply.

From what you say, I should have blank cells before the start of
production? Well, I do but still it slopes down. I really need the
vertical jump for this graph to make sense!

Just for information here is a little bit of the data just to show you
waht i mean

TIME .... 1983 1984 1985 1986
Russia - - 4830 5260
FSU 5640 6150 - -
Rest of FSU - - 1900 1920

So ideally the vertical line comes down in 1984 and the components
continue on thereafter.

Andy Pope wrote:


Hi,

If the line is rising from zero it would suggest that the periods before
real values contain a value or formula that the chart is treating as
zero. Either make sure the cells are really empty or your formula uses
NA() for non values.

Note this will not a vertical jump in the line rather the line will
simply being at the Y value for that period. If you want a vertical line
you will need to include an extra data point that has the period and a
zero value. You then need to ensure the x axis is being plotted as true
values and not categories other wise a double set of data for that
period will be shown.
Another way to get the dropline to zero would be to use custom Y Error
bars, where only that period has a drop of the value for that period.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

vertical line in area charts
 

Sorry missed the Area chart type in the question title.

Only way to get area chart to drop vertically is to include multiple
points for that period.
So you need to expand the years information, hopefully the text wrapping
doesn't destroy the layout.

01/01/83 01/01/84 01/01/84 01/01/85 01/01/85 01/01/86
Russia - - 0 0 4830 5260
FSU 5640 6150 0 - - -
Rest of FSU - - 0 0 1900 1920

You need to go to the Chart Options dialog and set the x axis to be Time
series rather than category. Set the Scale to be 1 years and format the
numbers to be yyyy to get the correct axis labels.

Cheers
Andy

wrote:
sorry i meant to say, '-' indicates a blank cell. the figures are just
rough but close enough (everything I have in the cells are values; no
formulas at all).

Andy Pope wrote:


Are those - place holders for posting purposes or what you currently
have in the cells?

wrote:

Howdy again

Thanks for the reply.

From what you say, I should have blank cells before the start of
production? Well, I do but still it slopes down. I really need the
vertical jump for this graph to make sense!

Just for information here is a little bit of the data just to show you
waht i mean

TIME .... 1983 1984 1985 1986
Russia - - 4830 5260
FSU 5640 6150 - -
Rest of FSU - - 1900 1920

So ideally the vertical line comes down in 1984 and the components
continue on thereafter.

Andy Pope wrote:



Hi,

If the line is rising from zero it would suggest that the periods before
real values contain a value or formula that the chart is treating as
zero. Either make sure the cells are really empty or your formula uses
NA() for non values.

Note this will not a vertical jump in the line rather the line will
simply being at the Y value for that period. If you want a vertical line
you will need to include an extra data point that has the period and a
zero value. You then need to ensure the x axis is being plotted as true
values and not categories other wise a double set of data for that
period will be shown.
Another way to get the dropline to zero would be to use custom Y Error
bars, where only that period has a drop of the value for that period.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

vertical line in area charts
 

Thanks so much!

It works like a dream and my chart looks amazing!

Thanks again