+ Reply to Thread
Results 1 to 2 of 2

Make graph and linear fit

  1. #1
    Registered User
    Join Date
    02-18-2015
    Location
    belgium
    MS-Off Ver
    Excel 2007
    Posts
    15

    Make graph and linear fit

    Hi all,

    I have an excel file with some glacier data for several years (-6000 to +4000) against elevation (2750 until 3650). However, I can't seem to create a graph just like in the example (see attachment). Can someone try to fix this and then also calculate a linear fit with Rē values on it.

    Thanks a lot!
    Attached Images Attached Images
    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: Make graph and linear fit

    Your sample file does not include your attempts at creating a chart, so it is difficult to know what you have tried and what you are having trouble with. Here's what I would have expected you to do:

    1) Select A2:B11 -> Insert chart -> Scatter chart -> lines without markers.
    2) With the chart selected, bring up the Select Data dialog.
    3) Select the existing data series -> Edit -> Change X values to be B2:B11 and Y values to be A2:A11.
    4) Add a new data series -> X values is C2:C11 and Y values is A2:A11
    5) Repeat step 4 for each data series (A2:A11 is always the Y values, and the desired column is the X values).

    Because you must add each series separately, it will be tedious, but should not be difficult.

    The main reason you must add each data series separately is because your chart uses the same Y value data but different X value data for each data series. Excel works best with data where each series has a common X value with different Y value data. If you can talk yourself into switching the axes -- so that column A is the horizontal X axis data and columns B+ are the separate vertical Y axis data, then you can simply select the entire table and insert the scatter chart directly.

    Are you wanting the best fit lines to be generated by the chart using the chart trendline feature, or do you prefer to perform the regression in the spreadsheet? I generally prefer to perform the regressions in the spreadsheet (using the LINEST() function https://support.office.com/en-us/art...a-fa7abf772b6d ). It should not be overly difficult to get slope, intercept, and R^2 from the LINEST() function and the right combination of relative and absolute references. If you want to use the chart trendline feature, then loop through each data series, adding and formatting the trendlines as desired.
    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. non linear 2nd axis for a graph
    By paascal in forum Excel General
    Replies: 1
    Last Post: 06-05-2014, 11:01 AM
  2. Line Scale in linear Graph
    By clonzhe in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-12-2013, 10:35 AM
  3. Linear + Log x Log x Log graph plot
    By Jacques Grobler in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-14-2012, 08:00 AM
  4. how do I graph a linear equation?
    By Throstle in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-16-2011, 05:04 AM
  5. How to make GRAPH with linear x axis
    By dcvrtila in forum Excel General
    Replies: 4
    Last Post: 01-25-2011, 07:31 AM
  6. [SOLVED] Powerpoint Graph- Shading in between two linear graph lines
    By TerenYoung in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-15-2006, 04:35 AM
  7. how do i graph multiple linear regression?
    By enortirol in forum Excel General
    Replies: 0
    Last Post: 02-21-2006, 09:25 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