+ Reply to Thread
Results 1 to 5 of 5

How to plot data in a quadrant based on text / values?

  1. #1
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    How to plot data in a quadrant based on text / values?

    Dear Excel specialists,

    I have a question.
    Currently I am making an inventory of various applications.
    All applications will be/are divided in 4 catagories (TIME = Tolerate, Invest, Migrate and Eliminate)
    I would like to plot these application names in a quadrant/matrix.

    My question is. How can I achieve this? I assume that I have to assign values to all applications, otherwise I cannot make a "dynamic" overview.
    I also don't want to have overlapping text in the quadrant/matrix, so I tried to assign an additional value to the application names.

    For example:
    Quadrant 1
    1,1 NameA
    1,2 NameB
    1,3 NameC
    and so on
    Quadrant 2
    2,1 NameA
    2,2 NameB
    2,3 NameC
    and so on

    Quadrant 3
    3,1 NameA
    3,2 NameB
    3,3 NameC
    and so on

    Quadrant 4
    4,1 NameA
    4,2 NameB
    4,3 NameC
    and so on


    I downloaded an example that works (please see attached file), based on the information on the left hand side.
    But I don't know how to get this working based on the information that I added on the right hand side (left side can be removed afterwards).
    Maybe it's just changing the scaling, but I am a little lost now.

    Thank you in advance for an answer!
    Attached Files Attached Files

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

    Re: How to plot data in a quadrant based on text / values?

    Checking chart type (scatter -- marker without lines) and unhiding the axis labels so I can see them, it looks like you want:

    "Tolerate" points to have X values between 0 and 5 and Y values between 5 and 10.
    "Invest" points to have X values between 5 and 10 and Y values between 5 and 10.
    "Migrate" points to have X values between 0 and 5 and Y values between 0 and 5.
    "Eliminate" points to have X values between 5 and 10 and Y values between 0 and 5.

    You haven't given any guidance on how to choose X and Y values, other than to keep points adequately spaced so they aren't too close together, so I cannot recommend anything specific.

    Once you have calculated/entered the desired X and Y values for each point so that it will show up in the correct quadrant of the chart, it should be as simple as adding chart data series to point to the ranges where you have entered/calculated these values. Remember to add data labels with values from the appropriate cells for each data series that you add.

    What specific help do you need to execute those steps?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    Re: How to plot data in a quadrant based on text / values?

    Hi Mr Shorty,

    Thank you very much for your help and sorry I wasn't very clear.

    The problem is that management only provides application names that are: (Tolerated, Invested, Migrated, or Terminated). There are no values, only names.
    The format is:
    1 column: Tolerate (applications in this specific column)
    1 column: Invest (applications in this specific column)
    1 column: Migrate (applications in this specific column)
    1 column: Eliminate (applications in this specific column)

    My goal is to plot/display the names in the assigned quadrant (below each other/preferably auto-scaled).
    I thought I'll add values to the application names (enumerated) so they end up in the right quadrant (without overlap). Currently, I am not able to do this as it should be.

    Please see the example provided. I hope that this (not working example) is more clear for you. :-)
    Attached Files Attached Files

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

    Re: How to plot data in a quadrant based on text / values?

    Again, without some guidance on how to choose X and Y values, I can't be very specific. Here's what I did:

    1) Select each axis and format so that the axis labels become visible so I can see what range of the numbers the chart is expecting. I see that each axis is expecting numbers between -1 and 11.
    2) You only have the one data series in the chart for columns A and B. You have entered -1 into A3 and 10.5 into B3, which would be in the far upper left corner of the chart. If I move the text box out of the way, I can see the data point with its data label in the upper left corner.

    At this point, it looks like the "tolerated" series is working just fine. I'm not sure why you chose X and Y values of -1 and 10.5. I can move the data point out from under the text box by entering different values into columns A and B. For example, I might enter 8.5 into B3 and see the data point just below the text box. I could enter -1 into A3:A6, and enter 8.5 into B3, 7.5 into B4, 6.5 into B5, and 6.5 into B6 to get 4 data points+data labels at the far left of the chart in the "tolerated" quadrant.

    In order to get the other quadrants, I would need to enter/calculate appropriate values in column D and E, G and H, and J and K and add those to the chart as additional data series. I would expect column D to have values between 5 and 11, column E would have values between 5 and 11, column G would have values between -1 and 5, column H would have values between -1 and 5, column J would have values between 5 and 11, and column K would have values between -1 and 5.

    It looks like you are well on your way. The hardest part, it seems, is choosing appropriate values for the X and Y values for each grouping.

  5. #5
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    180

    Re: How to plot data in a quadrant based on text / values?

    Hi MrShorty,

    I think I figured it out and can move forward again.
    Thank you very much for your support and good explanation!! :-)
    Adding rep and closing this thread.

+ 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. How to plot values based on x, y coordinates
    By rgillespie58 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-25-2022, 12:09 PM
  2. [SOLVED] Formula (?) to plot a predicted date based on two values
    By Dabooka in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2014, 09:40 AM
  3. [SOLVED] Quadrant colour based on cell value
    By RoyLittle0 in forum Excel General
    Replies: 14
    Last Post: 05-04-2013, 12:40 PM
  4. XY-plot diagram (text values on x-axis)
    By Lv27 in forum Excel General
    Replies: 2
    Last Post: 12-04-2012, 10:16 AM
  5. Replies: 2
    Last Post: 07-26-2012, 09:27 AM
  6. 4 quadrant graph with text not numerical data
    By Ariana in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-30-2012, 07:25 PM
  7. generating plot based on xstart xstop values
    By frankp in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-26-2007, 02:50 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