Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-colour data points in scatter chart (97)

    My data set has three columns: X, Y, and class. I can easily create a scatter chart showing the correct location of each data point by plotting the X and Y cooridinates. What I would like to achieve is for each data point to represent the class by varying the colour of each data point. For example, if the class was 1, then the point should appear as blue, 2's would appear red, 3's green, etc. I have a feeling this is impossible with just Excel, but I would like to know if anybody has a workaround.

    The only possible solution I came up with is to create (say) four new columns with one assigned to each class range. Then I could use an IF() function to populate the columns with <blanks> unless the class fell within the appropriate range. I've oversimplified this online example for clarity, so this solution won't really work with the real-world data.

    Anybody know of any freeware or shareware add-in solutions?

    TIA
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi-colour data points in scatter chart (97)

    I have used this technique exactly as you have outlined.
    If your X is col A, Y is col B, Class is col C from row 2 to whatever (row 1 is header)
    in d1 enter the value for class 1
    in e1 enter the value for class 2
    in f1 enter the value for class 3
    etc
    In d2 enter:
    =IF($C2=d$1,$B2,NA())
    copy this across the columns and down the rows
    Now you can plot ALL the cells (Col [img]/forums/images/smilies/cool.gif[/img] as well as a different fill color (E-whatever) for the points by changing the individual markers and colors.

    If this is too simplified a scheme for your data, how is your data setup? There are other formulas (you could even use a range instead of an individual value from row 1 to compare( this will do >= to the heading the current column, but less than the next column)
    =IF($C2>=D$1,IF($C2<E$1,$B2,NA()))


    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-colour data points in scatter chart (97)

    Steve
    Thanks for the confirmation. There are two problems with the specific data set. One is the number of rows of data -- there are several thousand rows, so I did not want to repeat the formula so many times (not an insurmountable problem). I am using autofilter to select which of the rows are plotted. Secondly, the data set really has at least three columns that could be considered the "class". Using the multi-column technique such as you described would make it a hassle to change which of the three columns was currently being plotted. For example, there are columns for moisture content, depth, and coverage -- I would like a flexible solution that would allow me to quickly/dynamically select one of the three columns as the current "class".

    I know that I can plot the results using a desktop GIS such as MapInfo or ArcView, but I was hoping to be able to plot it using Excel.


    I was unaware of the NA() function as you used -- I was going to use a null string. You solution looks better.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Multi-colour data points in scatter chart (97)

    Jack,

    Since you mentioned the possibility of an add-in, then maybe you are open to a code solution.
    The following is not complete as it should be tailored to your worksheet / charts.
    if your "Class" designation is recognized by Excel as the the Category labels (usually to the left of the data) then the code should work.
    You will have to provide the additional "Class" numbers and color designations in the Select Case portion of the code.
    In addition, the "Class" values should be formatted as text.
    *Select the chart first and then run the Sub...
    '--------------------------------------------------
    Sub ColorMarkers()
    Dim CatLabels As Variant
    Dim mSeriesPt As Point
    Dim M As Long
    Dim N As Long
    Application.ScreenUpdating = False
    For M = 1 To ActiveChart.SeriesCollection.Count
    For N = 1 To ActiveChart.SeriesCollection(M).Points.Count
    Set mSeriesPt = ActiveChart.SeriesCollection(M).Points(N)
    CatLabels = ActiveChart.SeriesCollection(M).XValues
    Select Case CatLabels(N)
    Case "1"
    mSeriesPt.MarkerForegroundColor = vbRed
    mSeriesPt.MarkerBackgroundColor = vbRed
    Case "2"
    mSeriesPt.MarkerForegroundColor = vbGreen
    mSeriesPt.MarkerBackgroundColor = vbGreen
    Case "3"
    mSeriesPt.MarkerForegroundColor = vbBlue
    mSeriesPt.MarkerBackgroundColor = vbBlue
    End Select
    Next
    Next
    Application.ScreenUpdating = True
    Set mSeriesPt = Nothing
    End Sub
    '---------------------------------------------------

    Regards,
    Jim Cone
    San Francisco, CA

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi-colour data points in scatter chart (97)

    1000s of rows might be a problem, but the formulas are simple so it might not make it too sluggish. Since you are really only using 1 formula, you might try using a named formula: put the formula into a "refers to" of a name (with the relative references) then you can use literally only 1 named formula and that should make it calc faster and use less memory

    Concerning the selection of multiple columns:
    Have the multiple columns, then have a NEW column of the SELECTED column (Index or Choose should work fine to extract it). Base the "extracted - separated class columns" on this new column of "selected data"

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-colour data points in scatter chart (97)

    Steve

    I've implemented your solution in a prototype -- see attachment. It works as-is, but I did not understand your instructions about a single formula and a "refers to". Would you mind seeing if you could improve my design. Also, I used the offset() function in the "PlotData" column -- not sure whether is any more or less efficient than the Choose() or Index() functions that you mentioned. I also used "brute force" copying of the formula into all the rows instead of the named reference as you suggested. Comments/critique?

    Thanks very much for your input.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi-colour data points in scatter chart (97)

    1) concerning my comment on named formulas:
    select the cell G10
    insert - name - define
    Give it a name like ColorCalc (the name is NOT important, whatever you want)
    In the "refers to" enter:
    <pre>=IF($F10>=G$9,IF($F10<H$9,$B10,NA()),NA())</pre>

    {note: this is the formula you currently have in G10!}
    <ok>
    Now in G10 enter the formula:
    <pre>=ColorCalc</pre>

    and copy this into the range G10:K19 (change as appropriate to fill your region)
    This essentially does the SAME thing as your formula, excel handles the named formulas better than the individual formulas. This should make it calc faster and use less memory than using all the individual IFs (especially if you thousands of calcs)

    2) In G9:K9 you used INDIVIDUAL formulas for each instead of copying across. Instead of using:
    =INDEX(Boundaries,$F$6,1)
    =INDEX(Boundaries,$F$6,2)
    =INDEX(Boundaries,$F$6,3)
    etc

    You could use in G9:
    =INDEX(G2:G4,$F$6)
    and copy this across the columns. Or you could put the numbers 1 - 5 in G9:K9 in G1:K1 and then use in G9:
    =INDEX(Boundaries,$F$6,G1)
    and copy it across
    YOu can use a completely generic one using(this does not require the numbered column in row 1) in G9:
    =INDEX(Boundaries,$F$6,1+COLUMN()-COLUMN(Boundaries))
    and copy it across the columns

    3) Instead of entering the column number in F6 you can have the user select from a combobox from the FORMs toolbar. I would use the forms toolbar combo instead of th control toolbox, since you can add it directly to the chart if you create the chart on a chart sheet, so the user can "on the fly" change the column that is plotted.

    4) I don't know which is better: Offset, choose, or index. I usually use index, just because it seems more intuitive to me (but that is me). I use offset primarily for "dynamic range names" (names that change size based on variables. I suggest you use whatever makes more sense to you.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-colour data points in scatter chart (97)

    <hr>excel handles the named formulas better than the individual formulas<hr>
    Great tip! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Gre

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-colour data points in scatter chart (97)

    Steve - great tips!! Learn something new every day!

    I had no idea that Excel could use the "named formulas" technique -- I will be able to use it in the original spreadsheet to replace four columns of calculations for the thousands of records. Cool! I was planning to use a combo box as you suggested -- the direct number input was just for the prototype.

    Thanks very much.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •