Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2
Default Scatter plot - Size of data points from spreadsheet value?

I have an XY scatter graph with some points in it.

My data in the spreadsheet looks like this:

[Title] [X-value] [Y-value] [size]

Data 1 | 10 | 10 | 1
Data 2 | 33 | 44 | 5

What I want to do is to change the size of the data point according to
the value I specify. Right now, I have to individually modify each
point and add a number corresponding to the size.

Is there anyway to take the size-value from the spreadsheet and change
the size of the point in the graph?

In the long run, I am also looking to modify the color.

If the solution requires some VBA coding, I am up for that. Any
suggestions are valuable!

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default Scatter plot - Size of data points from spreadsheet value?

Hi,

Try this code. Note that your example sizes will cause a problem. The
marker size is a integer value between 2 and 72.

The code also picks up the interior colour of the size cell so make sure
you apply a fill colour otherwise the markers will disappear.

'------------------------------------------
Sub SizeMarker()

Dim rngSizes As Range
Dim lngIndex As Long

' range containing marker size
Set rngSizes = Range("E4:E5")
With ActiveChart
With .SeriesCollection(1)
For lngIndex = 1 To .Points.Count
With .Points(lngIndex)
.MarkerSize = rngSizes.Cells(lngIndex).Value
.MarkerBackgroundColorIndex = _
rngSizes.Cells(lngIndex).Interior.ColorIndex
.MarkerForegroundColorIndex = _
rngSizes.Cells(lngIndex).Interior.ColorIndex
End With
Next
End With
End With

End Sub
'------------------------------------------

Have you looked at the Bubble chart? You can use a custom marker with
these and changing size will be linked to the cell. Colour on the other
hand will require multiple series and some conditional formula.

Here is some more information on custom markers.
http://peltiertech.com/Excel/ChartsH...omMarkers.html

Cheers
Andy

ferdy wrote:
I have an XY scatter graph with some points in it.

My data in the spreadsheet looks like this:

[Title] [X-value] [Y-value] [size]

Data 1 | 10 | 10 | 1
Data 2 | 33 | 44 | 5

What I want to do is to change the size of the data point according to
the value I specify. Right now, I have to individually modify each
point and add a number corresponding to the size.

Is there anyway to take the size-value from the spreadsheet and change
the size of the point in the graph?

In the long run, I am also looking to modify the color.

If the solution requires some VBA coding, I am up for that. Any
suggestions are valuable!


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2
Default Scatter plot - Size of data points from spreadsheet value?

Hi Andy,

Your solution works fine (after some tweaking)! I have several datasets
with only one point value so I need to loop through the datasets as
well. Anyway, it turned out great.

Your suggestion on bubble charts is interesting, i'll look into that.
Thanks again!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple scatter graphes how to plot 3 sets of data for x y on th. frances Charts and Charting in Excel 3 May 2nd 23 11:42 AM
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
32,000 data points error message DavidJ726 Charts and Charting in Excel 4 April 5th 06 08:21 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM


All times are GMT +1. The time now is 07:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"