Chart on Voter Turnout

[Max Daniel]

I am trying to make a chart with two series--one showing turnout in
presidential years and the other in mid-term elections.

In the first column I put the year (1960, 1962, 1964). In the second column
I put voter turnout in presidential years (1960, 1964). In column three in
put voter turnout in mid-term elections (1962, 1966). I select all the data.
The problem is in presidential years the data goes from, for example, 60% in
1960 down to zero in 1962 before going back to 60% in 1964. I want the data
line to go directly from 1960 to 1964 without dropping to zero in between.
Can anybody tell me how to do this? Thanks

 

[Jon Peltier]

If the cells for 1962 in presidential years are blank, select the chart,
choose Options from the Tools menu, and on the Chart tab, for Chart Empty
Cells As, choose Interpolated.

If the cells contain a formula that returns "" for the in between years,
which is not a blank, change "" to NA() in the formula. This puts an ugly
#N/A error in the cell (use conditional formatting to hide the error), but
it results in a line from the 1960 to the 1964 point.

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

 

[Max Daniel]

Thanks. It worked, but I have one more problem. Column one is the year,
column two is presidential year turnout, and column three is mid-term
turnout. When I select the chart and make a line chart, the X axis shows
numbers rather than years and list it as series one. How do I make it
category and show the years on the X axis?
Thanks very much for your help.

Max Daniel

If the cells for 1962 in presidential years are blank, select the chart,
choose Options from the Tools menu, and on the Chart tab, for Chart Empty
Cells As, choose Interpolated.

If the cells contain a formula that returns "" for the in between years,
which is not a blank, change "" to NA() in the formula. This puts an ugly
#N/A error in the cell (use conditional formatting to hide the error), but
it results in a line from the 1960 to the 1964 point.

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

 

[Jon Peltier]

Right click on the chart, choose Source Data from the pop up menu. On the
Series tab, select the first series and delete it, then click in the input
box for Category (X Axis) Labels, and select the range containing the years.

In the future, set up the data so that each set of Y data has a column
heading, and skip the heading over the category values:

Pres Mid Term
1960 65% #N/A
1962 #N/A 55%
1964 60% #N/A
1966 #N/A 50%

The blank cell (and it must be blank, not "") tells excel that the first row
and column are special.

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

Thanks. It worked, but I have one more problem. Column one is the year,
column two is presidential year turnout, and column three is mid-term
turnout. When I select the chart and make a line chart, the X axis shows
numbers rather than years and list it as series one. How do I make it
category and show the years on the X axis?
Thanks very much for your help.

Max Daniel

 

[Max Daniel]

Jon,

You have been very helpful. Thank you.

Right click on the chart, choose Source Data from the pop up menu. On the
Series tab, select the first series and delete it, then click in the input
box for Category (X Axis) Labels, and select the range containing the
years.

In the future, set up the data so that each set of Y data has a column
heading, and skip the heading over the category values:

Pres Mid Term
1960 65% #N/A
1962 #N/A 55%
1964 60% #N/A
1966 #N/A 50%

The blank cell (and it must be blank, not "") tells excel that the first
row and column are special.

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