xy scatterplots

  • Thread starter Thread starter chip smith
  • Start date Start date
C

chip smith

i developed an xy scatterplot graph on excel, using
percentages as my y axis and a logrithmic scale for the x
axis. but i need to find points on the graph. for example,
i need to find out what the x value is at 30%. my prof
told there is a way for excel to do it, but i need
help...any suggestions? thanx
 
Chip -

Here's an example, using some dummy data in A1:B6:

X Y
1 0.1
3 0.3
10 0.5
30 0.7
100 0.9

I put some formulas into B9:B11 (and labels in A9:A11) as follows:

Slope =SLOPE(B2:B6,LOG(A2:A6))
Intercept =INTERCEPT(B2:B6,LOG(A2:A6))
Correl =CORREL(B2:B6,LOG(A2:A6))

The formulas have computed these values:

Slope 0.399899525
Intercept 0.103760154
Correl 0.999874399

For a linear fit, Y = MX + B. For our relationship,
Y = M log X + B

To get Y for a given X, put X into cell A13, and put this formula in B13:

=B9*LOG(A13)+B10

To get X for a given Y, X = 10 ^ ((Y - B)/M), so enter Y in B15, and
enter this formula in A15:

=10^((B15-B10)/B9)

In my example, which is pretty close to linear, at Y = 30% (0.3), I
compute an X of 3.095443.

- Jon
 
Back
Top