Scatterplot Illustration Help

[Andyaf]

Hi!

I need some help creating and updating a scatterplot. On the first tab on the excel sheet attached, there are some transaction data. I’d like to create scatter plot from the data where the X axis is “Number of Weeks” and the Y Axis is “Amount.” The name would be the actual data point. Tab 2 has an example.

How can I automate this process? (i.e.: if I add new data, it will update the scatter plot).

Thanks for the help! :D

 

[NARAYANK991]

Hi ,

You call it a scatterplot , but you have used drawing objects to create this picture. Updating it automatically will require VBA , and going by the fact that the code will need to calculate the position of the bubble based on the values in columns C and D , it will be somewhat lengthy. Is it worth it just for the sake of appearances ?

Why not use a standard bubble chart , where you can use dynamic named ranges to take care of fresh data ?

Narayan

 

[Andyaf]

Hi ,

You call it a scatterplot , but you have used drawing objects to create this picture. Updating it automatically will require VBA , and going by the fact that the code will need to calculate the position of the bubble based on the values in columns C and D , it will be somewhat lengthy. Is it worth it just for the sake of appearances ?

Why not use a standard bubble chart , where you can use dynamic named ranges to take care of fresh data ?

Narayan

Thanks for the reply!
I used drawing object to create an illustration, can you run me through how I can create the bubble chart using dynamic named ranges?

 

[NARAYANK991]

Hi ,

See if this is acceptable. There are some restrictions :

1. The color of each bubble is not what you have mentioned in column B ; this is automatically determined by Excel. Doing what you want will need some coding.

2. The labelling of each data point is manual ; if this is to be done more easily , you can think of the add-in by Rob Covey ; see this link :

http://www.appspro.com/Utilities/ChartLabeler.htm

Try adding more points and see if it works the way you want it to.

Narayan

 

[Andyaf]

This helps alot

Hi ,

See if this is acceptable. There are some restrictions :

1. The color of each bubble is not what you have mentioned in column B ; this is automatically determined by Excel. Doing what you want will need some coding.

2. The labelling of each data point is manual ; if this is to be done more easily , you can think of the add-in by Rob Covey ; see this link :

http://www.appspro.com/Utilities/ChartLabeler.htm

Try adding more points and see if it works the way you want it to.

Narayan

Thanks, this helps alot!

Would it be possible to replace the X axis with words instead of Numbers? ie: like the file attached?

 

[NARAYANK991]

Hi ,

I am not able to understand ; because the Amount column has text instead of numbers , Excel takes all the values as 0 , and all the bubbles are along the X-axis ; what exactly should the X-axis use instead of the values from the Week column ?

Narayan

 

[Andyaf]

The X Axis should not be numbers, it should be text options. Can this be done?

 

[Andyaf]

The X Axis should not be numbers, it should be text options. Can this be done?

Hi ,

I am not able to understand ; because the Amount column has text instead of numbers , Excel takes all the values as 0 , and all the bubbles are along the X-axis ; what exactly should the X-axis use instead of the values from the Week column ?

Narayan

Hi ,

I am not able to understand ; because the Amount column has text instead of numbers , Excel takes all the values as 0 , and all the bubbles are along the X-axis ; what exactly should the X-axis use instead of the values from the Week column ?

Narayan

The X Axis should not be numbers, it should be text options. Can this be done?

 

[NARAYANK991]

Hi ,

I think you should first decide what kind of a chart , if at all it needs to be a chart , is required.

See this file and comment.

Narayan