+ Reply to Thread
Results 1 to 6 of 6

How can I make this chart?

  1. #1
    Registered User
    Join Date
    11-04-2016
    Location
    Czech Republic
    MS-Off Ver
    2013
    Posts
    3

    How can I make this chart?

    Hello, I would like to make this chart

    fyzika.PNG

    In excel I have values in the table and chart from them but I don't know how to make lines and find their intersection.

    excel.PNG

    Could you help me please?

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

    Re: How can I make this chart?

    I'm sure we can help.

    As far as the chart goes -- what I see is a basic scatter plot. There are three data series (meaning there are three sets of data in the spreadsheet) -- one for the S-curve, one for the horizontal line, and a third for the sloped line. Your question indicates that "don't know how to make lines and find their intersection." Here's how I would expect to do this:

    1) Regression step to find the slopes and intercepts of the two lines.
    1a) Extract data from the raw data for the horizontal line.
    1b) Perform regression (LINEST(), or SLOPE()/INTERCEPT(), or other) on the extracted data to get slope and intercept. There may need to be some thought into whether you intend to coerce the slope of this line to 0 or not.
    1c) Repeat steps 1a and 1b for the sloped line based on data in the middle of the curve.
    Alternate 1) or maybe you want to perform an overall regression that fits all of the data to an s-shaped curve, then use calculus on that to compute slope and intercept for the end points and the "inflection" center point.

    2) With slope and intercept for each line, you can readily compute the point of intersection using the strategies you (hopefully) learned long ago in algebra class for solving systems of equations (http://www.purplemath.com/modules/systlin1.htm ).

    3) To add the lines to the chart, you can compute a couple of points for each line using the slope and intercept in the spreadsheet. Then add those points to the chart as additional data series.

    With the process broken down into steps like that, is there a particular step or part that you are stuck on? Step 1) seems the least well defined at this point, since I am not certain exactly what approach or algorithms you would want to use to determine the slope and intercept of the two lines. Once that part is done, step 2 and 3 are fairly straightforward.
    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
    11-04-2016
    Location
    Czech Republic
    MS-Off Ver
    2013
    Posts
    3

    Re: How can I make this chart?

    Hello,

    First of all, I would like to thank you for your response.

    I tried it but unsuccessfully. I have to make 3 charts (when Ua=8V, Ua=10V, Ua=12V) and in these charts do those lines.
    I can upload the file and you can try it if you have time.

    Thank you


    The file: https://drive.google.com/file/d/0B2w...ew?usp=sharing
    Upload to post doesnt work.

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

    Re: How can I make this chart?

    Upload to post works, if you click Go advanced -> Manage attachments (below the edit window) -> and follow instructions in the file uploader.

    I'm still not sure how you want to tackle the regression step (step 1). You have a slope and intercept function in the spreadsheet, but they perform the regression over the entire data set. What are you trying to see by performing the regression over the entire data set? I would have expected a "piecewise regression" where you compute the slope and intercept over, for example, rows 6 to 11 for the horizontal line and over rows 13 to 17 (maybe) for the sloped line. Do you understand why I might choose those rows for the piecewise regression steps? Obviously, you understand how to enter the SLOPE() and INTERCEPT() functions, can I assume that you can edit the ranges to reflect the desired inputs to each regression?

  5. #5
    Registered User
    Join Date
    11-04-2016
    Location
    Czech Republic
    MS-Off Ver
    2013
    Posts
    3

    Re: How can I make this chart?

    Well, I have the chart made from values in Ic and Ia.

    If you look at this chart you can see two lines and their intersection. In their interseciton is the value I0 which I need to know. That's what I need to do in my chart. Do you understand me?

    fyzika.PNG
    Attached Files Attached Files

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,171

    Re: How can I make this chart?

    Administrative Note:
    • We would love to continue to help you with your query, but first, before we can proceed…
    • Please see Forum Rule #1 about proper thread titles and adjust accordingly...
    HTH
    Regards, Jeff

+ 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. Line chart too wide (52 weeks) How to make it the line chart automatically?
    By sovietchild in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-26-2016, 08:20 PM
  2. How do I double click a bar in a bar chart and make another bar chart?
    By bishoposiris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2016, 11:43 AM
  3. [SOLVED] how to make the chart
    By dolphino in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-18-2015, 11:31 PM
  4. Replies: 3
    Last Post: 05-05-2015, 12:43 PM
  5. Trying to Make a Chart
    By EricJSanders in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-09-2013, 10:32 PM
  6. Replies: 2
    Last Post: 08-30-2012, 07:12 AM
  7. Make a Chart
    By niladri20005 in forum Excel General
    Replies: 1
    Last Post: 06-02-2011, 01:36 PM

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