Pareto question - no. values vs %s

[halesowenmum]

Hi

I have a table with info as per:
[TABLE="width: 493"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Dept[/TD]
[TD]Total No. visitors checked out by reception[/TD]
[TD]Total No. Visitors who returned with 7, 14 or 28 days[/TD]
[TD]Returning Visitors as a % of Total Checked Out By Reception[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dept 1[/TD]
[TD]2000[/TD]
[TD]1500[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dept 2[/TD]
[TD]1000[/TD]
[TD]20[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Dept 3[/TD]
[TD]50[/TD]
[TD]49[/TD]
[TD]98%[/TD]
[/TR]
</tbody>[/TABLE]

Because the departments have different numbers of visitors, setting column C as what would be the 'Pareto line' (ie started off as a Combined Column Chart but then taking the pertinent data as a secondary axis in the form of a Line Chart with Stops) would be misleading because although 1500 is the biggest number, as a proportion of the total compared to other departments, it's only when you look at the Returning Visitors % that gives the true picture of which one is accounting for more returning visitors (let's imagine they are highly disgruntled so that we understand that 98% is a rubbish figure and therefore the lower the figure the better things are - hope that makes sense!!).

What I want to do therefore is have the column area of the chart using Columns A and B or just B, and the second set of data that's going to end up as a secondary axis to give a pareto line would be the data from column D.

However, that doesn't give me a nice pareto line that goes from the bottom left of the chart up to the top right hand corner of the chart - the columns are all in a lovely order (I've got the largest values sorted first) but then of course the %s line goes up and down up and down up and down all over the shop.

I guess my question is, is it possible to display the data like this because just looking at the numbers makes no sense as the total number in each department shows very differing volumes of activity and only the % gives an accurate result - but is possibly not Pareto-able....

Can I do this in any way that will show me which departments (regardless of volume) are the 20% with the highest numbers of visitors coming back (because that evens out their differing activity levels), and have it look anything like a Pareto????

Crikey I hope that makes some sense!!