+ Reply to Thread
Results 1 to 16 of 16

Scatterplot chart (matrix) with conditional format

  1. #1
    Registered User
    Join Date
    10-14-2009
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    14

    Scatterplot chart (matrix) with conditional format

    I want to create a scatter plot graph with conditional format.
    Each point should have a color based on the amount of involvement
    Also is it possible to create this without adding each point individually?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,352

    Re: Scatterplot chart (matrix) with conditional format

    The usual starting point for conditional formatting of charts is this: https://peltiertech.com/conditional-...-excel-charts/ Would it be acceptable to implement something like that in your sheet?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-14-2009
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Scatterplot chart (matrix) with conditional format

    Thanks for your reply
    However if i would implement this solution I am not able to label each point in the graph as seen in the example.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,352

    Re: Scatterplot chart (matrix) with conditional format

    Can you explain why not? I don't see anything that would prevent you from adding data labels to each data series or some other strategy for adding data labels.

  5. #5
    Registered User
    Join Date
    10-14-2009
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Scatterplot chart (matrix) with conditional format

    Perhaps you can give me one example in my attached sheet so i can see if i understand correctly.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,352

    Re: Scatterplot chart (matrix) with conditional format

    I cannot, because my older version of Excel (assuming you have upgraded to something way newer than the Excel 2003 currently shown in your profile) does not support using "values from cells" as data labels. Your current setup uses 4 data series, a single point for each data series, and a single data label (value from corresponding cell in column A) for that point. Using Peltier's approach, you will end up with a chart with 3 data series (multiple points or NA() values in each data series) from 3 helper columns that pulls from the original data based on involvement. Once you have set up the three data series as outlined in Peltier's tutorial, adding data labels with values from cells should be as easy as selecting each data series, adding the data labels, and specifying column A as the source for the data label text. Can I assume, since you've already added data labels in the current file that you are already familiar with how to add data labels to a data series?

  7. #7
    Registered User
    Join Date
    09-03-2016
    Location
    Catania, Italy
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Scatterplot chart (matrix) with conditional format

    Hello
    With VBA?

    Try this macro for the example posted
    Please Login or Register  to view this content.
    Let me know. Hello,
    Mario

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,102

    Re: Scatterplot chart (matrix) with conditional format

    MS documentation on how to set data label value from cells
    https://support.microsoft.com/en-us/...2-f467c9f4eb2d
    Cheers
    Andy
    www.andypope.info

  9. #9
    Registered User
    Join Date
    09-03-2016
    Location
    Catania, Italy
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Scatterplot chart (matrix) with conditional format

    Hello
    I think is better
    Please Login or Register  to view this content.
    Let me know. Hello,
    Mario

  10. #10
    Registered User
    Join Date
    10-14-2009
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Scatterplot chart (matrix) with conditional format

    I am unfamilliar with macro's. Is this the only way? I tried to copy and paste the text, but when I change something it does not change in the graph.

  11. #11
    Registered User
    Join Date
    09-03-2016
    Location
    Catania, Italy
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Scatterplot chart (matrix) with conditional format

    Hello
    Associate the macro with a button and click on it every time there is a change in the sheet.
    It is possible to make the change automatic if the data will always be in columns A and B (I see to adapt the code and then publish it) but if you are unfamiliar with VBA it is best to stop here.

    Hello,
    Mario

  12. #12
    Registered User
    Join Date
    09-03-2016
    Location
    Catania, Italy
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Scatterplot chart (matrix) with conditional format

    Hello
    This code is to be entered in the class module of the sheet concerned.
    Please Login or Register  to view this content.
    Any variation in the interval from A2 to C and the last line evaluated will bring about an adjustment of the Graph.
    I am attaching your file with the inserted macro.
    Let them know. Hello,
    Mario
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,352

    Re: Scatterplot chart (matrix) with conditional format

    I am unfamilliar with macro's. Is this the only way?
    Peltier's approach to this doesn't use any macros, so, no, macros are not the only way. Often it is really a personal preference of programming language. If you don't like using VBA, go back to Peltier's tutorial, set up your three helper columns, point your chart to those three columns, then apply data labels to the three data series. If you have trouble with any of those steps, let us know where you are stuck and we'll help.

  14. #14
    Registered User
    Join Date
    10-14-2009
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Scatterplot chart (matrix) with conditional format

    I got it to work with the data series!!!!
    Now when I change the label for a data point and I also change the cell on which conditional format is applied, the label is set back to the original value.
    How can I prevent this from happening?

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,352

    Re: Scatterplot chart (matrix) with conditional format

    I am unable to imagine what you have done that causes a data label in the chart to not accurately reflect the text of the cell the data label points to. Could you upload your latest file with your chart so we can see what you've done?

  16. #16
    Registered User
    Join Date
    10-14-2009
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Scatterplot chart (matrix) with conditional format

    I attached an excel file with my problem. When I change involvement in stakeholder interest tab, the name in the graph changes.
    Even though I linked the label to the specific name.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] 9 Cells Matrix / GE Matrix chart hellp
    By vani2004 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-20-2018, 02:34 PM
  2. Matrix bubble chart conditional formatting
    By ROHANROKS in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-10-2017, 11:26 AM
  3. Conditional highlighting on my XY scatterplot
    By russwongg in forum Excel General
    Replies: 2
    Last Post: 08-04-2014, 06:29 AM
  4. VBA Format for Scatterplot
    By kal5092 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-02-2010, 01:43 PM
  5. Pivot Chart for Scatterplot
    By pickslides in forum Excel General
    Replies: 4
    Last Post: 03-22-2010, 05:26 PM
  6. [SOLVED] Scatterplot chart area changes size on different systems
    By Hubris00 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-27-2005, 06:05 PM
  7. [SOLVED] How do I show the maximum point on a scatterplot chart?
    By Chief in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-08-2005, 11:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1