Adding a manually controlled Line to XY scatter

[MartinW]

Hi Group,

Given an XY scatter plot with the following data;

X-Values
Penetration(mm)
0.0
0.5
1.0
1.5
2.0
2.5
3.0
3.5
4.0
4.5
5.0
5.5
6.0
6.5
7.0
7.5

Y-Values
Force(newtons)
45
620
2941
5709
7960
9907
11626
13177
14394
15523
16595
17474
18399
19242
20021
20770

When plotted you can see that the curve starts off concave and
then changes to convex. The concave section has to be adjusted
out by a process called zero correction. This is done by drawing
a line tangential to the steepest part of the curve and projecting
it down to the X-axis. In this example the line crosses the X-axis
at 0.5, so 0.5 will become the new origin point and the X-axis values
will continue on from there. Zero corrections can be from 0.0 to a
maximum of 0.5, anything over is accepted as 0.5.
At present I just draw a line over the graph, manoeuvre it into place
with the handles and read off the visual result and input it into the
spreadsheet. Not all plots are as neat as this one so it is a judgement
call as to where the line should be placed.
What I would like to do is be able to put the line in place adjusting
the slope and horizontal alignment with the arrows on the keyboard
and have a cell calculating the X-value where Y=0.

Is this possible?.
P.S. For anyone who knows Soils Technology what I have described
is a CBR plot.

Sincerely
Martin

 

[Tushar Mehta]

I am not quite sure what happens when you enter the new x value into the
worksheet but I imagine one of the following, or some combination of two
or maybe all three, should be useful:

Interactive graph analyzer
http://www.tushar-mehta.com/excel/software/utilities/iga.html

Read Chart Value
http://www.tushar-mehta.com/excel/tips/read_chart_value/index.html

and

Interactive Chart
http://www.tushar-
mehta.com/excel/software/interactive_chart_display/index.html

 

[MartinW]

Thank you Tushar,

Your links helped me to see that I can input the line as a second
series and I can manipulate that through the source data to solve
part of the problem, however, I would still like to manipulate
the line with the arrow keys. We have a very old DOS based
program that does this part of the procedure very well, unfortunately
that is about the only thing that it does do well, the rest is frustratingly
painful.

Any other ideas?

Regards
Martin