+ Reply to Thread
Results 1 to 2 of 2

XY Scatter plot with excluded data

  1. #1
    Registered User
    Join Date
    05-14-2024
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    1

    Question XY Scatter plot with excluded data

    I have this data, with the frequency of specific categories and the years they occur in.

    A B C D E F G H I
    1272 58.2 25.4 6.1 3.0 2.0 4.1 0.0 0.0 1.0
    1312 51.0 18.7 8.7 4.7 5.4 4.0 2.7 2.0 2.7
    1360 33.6 35.0 11.2 6.3 9.1 2.1 0.7 1.4 0.7
    1379 40.0 26.7 20.0 0.0 0.0 0.0 6.7 6.7 0.0
    1381 41.9 33.6 10.5 4.3 5.5 1.6 0.6 0.6 1.1

    I want to create a XY scatter plot with trendlines, with years along the X axis and frequency along the Y axis. Each category wants to have its own individual trendline. However, I specifcally want to exclude 1379 from the trendlines it draws. Instead, I want 1379 to be graphed effectively as a secondary scatter plot without lines, so that I can see if my data for 1379 is consistent with data for the other years.

    I've attempted to set 1379 as a seperate series but that doesn't work as the series are the categories of A-I. The only solution I can think of is to create two seperate graphs and stack them on top of each other and adjust the transparancy, although this is obviously not an ideal solution as it won't be 100% accurate.

    Any help would be appreciated.
    Last edited by names; 05-15-2024 at 10:51 AM. Reason: Solved

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

    Re: XY Scatter plot with excluded data

    I find that many charting questions end up being more about what is happening in the spreadsheet. Can I assume you are allowed to rearrange the data into something that will make the chart easy to create?

    Copying your data into a spreadsheet, I:

    1) Move the 1379 row so it is several rows below the main data.
    2) Select the 1272 to 1381 data and insert the scatter chart. My copy of Excel guesses incorrectly that I want "series in rows," so I execute "switch row/column" to get the desired frequency versus year.
    3) Select the 1379 row -> ctrl-c to copy to clipboard -> select chart -> paste special as new series with category/x values in first column.
    4) Add trendlines to the four data series that require trendlines, and format other chart elements as desired.

    Would a strategy like that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Replies: 2
    Last Post: 11-08-2019, 04:57 AM
  2. Scatter plot with two X axis data
    By hityou123 in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 02-02-2015, 08:26 PM
  3. Scatter plot not comparing data in X and Y against each other
    By Jeffthamm in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-31-2013, 07:37 AM
  4. Replies: 2
    Last Post: 07-26-2012, 09:27 AM
  5. Creating a Scatter Plot for any # of data
    By TwoyTaylor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2008, 10:07 AM
  6. [SOLVED] Scatter Plot Data Labels
    By Mike K. in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-22-2008, 10:12 AM
  7. Can I copy x-y scatter plot data direct from one plot to another?
    By Chris in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-03-2005, 09:05 AM

Tags for this Thread

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