Two series, two scales on single axis ?

[Ian]

Hi - I have a chart with sales people on the x-axis, #'s sold on the left
hand y-axis (columns) and average gross on the right hand y-axis (line).
I've always wanted to also show total gross on the right hand y-axis (also
line) i.e. two series on the right hand y-axis which can't sensibly be done
using the same scale because the averages are (say) 500 to 3000 and the
totals are (say) 10,000 to 100,000.
I haven't got a clue how and have searched the web unsuccessfully. Surely
there's a way?

A data table might look like:

spsn,numbers,average,total
A,10,1500,15000
B,13,1000,13000
C,20,1750,35000
D,5,2500,12500

and what I'm looking for something like:
20























18























16









40000













14









30000
















12









20000













10









0













8























6












500













4









300













2









100















A


B


C





Thanks (in grateful anticipation)
Ian.

 

[Ian]

sorry about the html attempt to show graph - seems it didn't work

 

[Jon Peltier]

I would use three separate charts, each around 1/3 the height of the total.
I'd use an approach like this to generate a chart with three panels:

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

The vertical axes often take a little more effort than this simple example.
It's a little work to set it up, but the end result looks good.

- Jon

 

[Ian]

Thanks Jon - this looks great. Won't have a chance until weekend to get into
it but looks like it will do the trick.
Much appreciated, Ian.