XL bubble chart - places, months, values 1

[Welshbird]

I have a sort of 3D data set which has customers, months and order qty. there are 35 customers, 16 months and order qty is between -10 and 28. The figures just aren't large enough for a stacked bar or column (certainly for the number of customers) so I pondered using a bubble chart. I'd want an x-axis of months, a y-axis of customers and the order qty as the bubble size.

I just don't see that there is a way of puting customers up the y-axis.

Any suggestions chaps?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Please post some sample data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Add a column of sequence numbers, 1,2,3 etc, representing the customers

Plot to cust SEQ on y, date on x, ord qty on size.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Thanks Skip - I have tried that, but as this is something I'll need to recreate regularily, I want to be able to show the customer name on the y axis rather than the number. It certainly gave me what I wanted to see in my bubbles though..

Sample data
[tt]
Customer 1 Apr-09 6
Customer 2 Apr-09 11
Customer 1 Jun-09 -4
Customer 2 Jul-09 16
etc
[/tt]



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

A bubble chart is similar to an x-y scatter chart. NEITHER has a 'CATEGORY' axis. BOTH axes are VALUE axes. So how else would you propose to show the customers?

You CAN show the customer as a Data Label, using VBA. BTW,

http://www.j-walk.com,

Walkenbach has a Chart addin that does this.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Alternatively, you could display the customers on the sheet and superimpose the chart so that the y-axis coincides with the rows.

I've done this, formatting the Chart Area patterns tab with border: NONE and area: NONE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What seems more like you want might be a BAR chart (horizontal)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Thanks for all sugggestions - I'll have another look and see what I can come up with.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

I don't think I can have a bar chart - I've really got two category dimentions and a measure which is why I was trying to do a bubble graph.

I might need to go and soak in a bathtub and seek inspiration!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

yea, yer right. Bar chart is not the answer.

When you say, "recreate," can that not be done from a template?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

It could be certainly - and that would be my plan. the only thing is to design the very first graph properly to show the best info before create the template.

(You might be able to tell that I've just started a new job, so am trying to be just a bit impressive!)

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Get a copy of John Walkenbach's Excel Charts. LOTS of great info to make impressive 'impossible' charts.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

I might look into that Skip - thanks for all your help.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen