1 Attachment(s)

Adding information to a chart

Please refer to picture:

Attachment 662421

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

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?

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.

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

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.

Quote:

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.

Quote:

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

Quote:

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?