3 series and dual axes chart

[Westbury]

I'm trying to include 3 data series in one chart.

Series 1 uses the x,y axes [date, value]
series 2 uses the same date scale but a secondary y axis for the values.

I'm stuck trying to get series 3 to use a secondary date scale, which I have displayed along the top of the chart, and to use the secondary y axis.

Grateful for any help.

Here's a link to the file
Geoff

 

[Sergius]

Hello! No access to the file.

 

[Westbury]

I think I've corrected the link and added a bit more description below.


I'm trying to include 3 data series in one chart.

Series 1 uses the x,y axes [date, value]
series 2 uses the same date scale but a secondary y axis for the values.

I'm stuck trying to get series 3 to use a secondary date scale, which I have displayed along the top of the chart, and to use the secondary y axis shared with series 2.

The reading dates for all the series are within the UK financial year 1 April to 31 March but series 3 dates may not always coincide with reading dates of series 1 & 2.
Hence my need for a secondary date scale.

Grateful for any help.

Here's a link to the file
Geoff

 

[Sergius]

You can't do what you want. One option is to make two diagrams, and then overlay them on top of each other (in layers). See attached picture.

 

[myall_blues]

You could just create one date column containing all the dates, then make the relevant data value =NA() when it doesn't have a value for that date. For examples, see rows 24-29 in image.

See link to file.

 

[Westbury]

Sergius and Myall_blues, thanks for your interest and suggestions. I can't accommodate blank cells or non dates as it has knock on effects in the broader file.

 

[myall_blues]

How about this then, which uses your original data arrangement?

 

[Westbury]

What have you done?

 

[myall_blues]

To create this graph:

• Select the first data set B4:D43
• Insert -> Chart group -> Line or Area chart -> Line with markers
• Select the second data set G4:H38 and copy.
• Select the chart, then Paste->Paste Special and accept all the defaults. Click ok.

• Right click on any of the chart series and select 'Change series chart type...'
• Tick the box for the secondary axis for the est kWh year total series and click ok.

• If you still want the secondary Y axis (it's the same as the primary but you can have different time periods between the markers for example then Chart Design menu -> Chart layouts group ->Add chart element ->Axes ->Secondary horizontal.
• Do any additional formatting/scaling of axes, series formatting, axes and chart titles etc and voila!.

Link to file.

 

[Westbury]

Thank you for the explanation and your interest. It's a whole new world of charts....