+ Reply to Thread
Results 1 to 5 of 5

Adding information to a chart

  1. #1
    Registered User
    Join Date
    11-17-2019
    Location
    California, United States
    MS-Off Ver
    Office 365
    Posts
    4

    Adding information to a chart

    Please refer to picture:
    asdf.jpg

    1) How do I include multiple best fit lines (blue lines)?
    2) How do I include coordinates of their intersecting points?
    3) How do I indicate the center points of the lines with shapes, their coordinates, and include a legend?
    4) How do I include major and minor gridlines?
    5) How do I find the curve of the 1st derivative of the original curve, include the new curve on the same chart as before, and include a y-axis on the right sight of the chart labeled ΔpH/ΔV? (Not shown).

    Thank you
    Last edited by gcon22; 02-12-2020 at 03:48 PM.

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

    Re: Adding information to a chart

    For the gridlines, follow the instructions here to add gridlines https://support.office.com/en-us/art...5-269646f0deaa Then look for the available formatting options.

    For the other stuff, I would suggest that most of the work is in the spreadsheet. In the spreadsheet, compute the necessary data for each line then add each additional element to the chart as additional data series and formatted as you want.

    1) For the different linear best fit lines, use TREND(), FORECAST(), or LINEST() to compute the desired (x,y) values along each line in the spreadsheet. Then add as new data series.
    2) Using your favorite strategy for solving systems of equations, compute the coordinates of the intersection points in the spreadsheet (using LINEST() for the regressions in step 1 may be preferable for this step). Add these points as a new series, and add data labels (if you want to show (x,y) values in the chart as your picture shows).
    3) Again, you will need to calculate the "center" point of each line (usually a couple of AVERAGE() functions will do it, once you identify which values to average) in the spreadsheet. Then add these points to the chart as a new series and format as desired.

    Which part do you have trouble with?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

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

    Re: Adding information to a chart

    You added part (5) while I was typing, but it is basically the same answer. You need to calculate the 1st derivative in the spreadsheet using your favorite strategy, then add those values as a new data series and format the series to be on the secondary axis.

  4. #4
    Registered User
    Join Date
    11-17-2019
    Location
    California, United States
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Adding information to a chart

    Thank you for your response. Here is the data set that I'm working with. When plotted, it will create a similar curve to above. I'm trying to add best fit lines along the "flat" portions of the curve, and I don't know how I would be able to compute the necessary data for each line. Also, still unsure about how to find the intersection points of each line.

    (data set will appear off, but there are two columns (vol vs pH), example of data values of first row is 0.1 vs 2.12)

    vol (mL) pH
    0.1 2.12
    0.6 2.15
    1.1 2.19
    1.6 2.23
    2.1 2.26
    2.6 2.3
    3.1 2.33
    3.6 2.37
    4.1 2.41
    4.6 2.44
    5.1 2.48
    5.6 2.51
    6.1 2.56
    6.6 2.59
    7.1 2.63
    7.6 2.67
    8.1 2.72
    8.6 2.76
    9.1 2.81
    9.6 2.85
    10.1 2.9
    10.6 2.96
    11.1 3.02
    11.6 3.09
    12.1 3.15
    12.6 3.24
    13.1 3.33
    13.6 3.45
    14.1 3.6
    14.6 3.82
    15.1 4.25
    15.6 7.43
    16.1 8.1
    16.6 8.34
    17.1 8.52
    17.6 8.66
    18.1 8.75
    18.6 8.84
    19.1 8.91
    19.6 8.98
    20.1 9.04
    20.6 9.1
    21.1 9.15
    21.6 9.2
    22.1 9.25
    22.6 9.3
    23.1 9.33
    23.6 9.38
    24.1 9.42
    24.6 9.46
    25.1 9.49
    25.6 9.53
    26.1 9.57
    26.6 9.61
    27.1 9.64
    27.6 9.68
    28.1 9.72
    28.6 9.76
    29.1 9.79
    29.6 9.83
    30.1 9.87
    30.6 9.91
    31.1 9.96
    31.6 10
    32.1 10.05
    32.6 10.1
    33.1 10.15
    33.6 10.21
    34.1 10.27
    34.6 10.33
    35.1 10.41
    35.6 10.5
    36.1 10.61
    36.6 10.72
    37.1 10.87
    37.6 11
    38.1 11.13
    38.6 11.22
    39.1 11.28
    39.6 11.33
    40.1 11.38
    40.6 11.41
    41.1 11.44
    41.6 11.47
    42.1 11.49
    42.6 11.5
    43.1 11.52
    43.6 11.53
    44.1 11.54
    44.6 11.55
    45.1 11.56
    45.6 11.57
    46.1 11.57
    46.6 11.58
    47.6 11.59
    48.6 11.6
    Last edited by gcon22; 02-12-2020 at 10:04 PM.

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

    Re: Adding information to a chart

    So the question is not so much about adding things you know how to do to a chart, but how to calculate the different parts of the problem.

    As this looks a lot like a homework problem, I hesitate to be too specific in my answers.

    How much needs to be automated? If this is a homework problem, it is probably something you will do a few times during this unit, then be done with it for a while. In that case, I wouldn't worry too much about automating every little step.

    I'm trying to add best fit lines along the "flat" portions of the curve,
    The first part of this is identifying these "flat" portions and then separating out the data for each "flat" portion. How are you identifying the different "flat" portions? The first thing I would do is identify a "flat" portion, select the data for that flat portion, copy -> paste into another part of the spreadsheet. Repeat for each flat portion.

    and I don't know how I would be able to compute the necessary data for each line.
    As near as I can tell, all you should need are slope and intercept for each line. You can use the SLOPE() and INTERCEPT() functions for just this purpose.
    SLOPE() function help file: https://support.office.com/en-us/art...a-61d7e01276b9
    INTERCEPT() function help file: https://support.office.com/en-us/art...3-3bca70bf63ef

    Also, still unsure about how to find the intersection points of each line.
    I expect this is more of an algebra question than a programming question. Once you have slope and intercept, each line is described by the standard y=mx+b. From there, review your strategies for solving systems of equations. I find this a helpful tutorial: https://www.purplemath.com/modules/systlin1.htm Once you've done the algebra, then you can enter the formula into the spreadsheet.

    That should answer that set of questions. What parts do you need more help with?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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