Excel gurus

Soldato
Joined
18 Oct 2002
Posts
3,074
Location
manchester
I'm not the best with excel and i'm wondering if there is a simple way to do this

image.jpg


What I need is to input a number say 45 as shown and then it automatically reads across to the trend line and then gets the number from the X axis. is there any simple way to do this.
 
size / log of size / distance (mm)

3000 / 3.477121255 / 22
2000 / 3.301029996 / 26.5
1650 / 3.217483944 / 28.5
1000 / 3 / 36
850 / 2.929418926 / 38.5
650 / 2.812913357 / 42.5
500 / 2.698970004 / 46
400 / 2.602059991 / 48.5
300 / 2.477121255 / 51.5


Is the basic data. Then I need to plot a lot of points from Y axis to the trend line then down to the X axis, as shown in the picture.

In effect I need to input a number from the y axis (distance migrated) excel reads across to the trend line to where it intercepts it outputs the x axis value (log of base pairs).
 
Last edited:
The only way I can think of would be to create a column of y values you want the data known for then rearragne the equation for x and work from there eg

46 (A1-122.46)/-28.643
45 (A2-122.46)/-28.643

etc. Once you put the equation in one column you can drag it down to repeat. I guess you know how to do so?
 
The only way I can think of would be to create a column of y values you want the data known for then rearragne the equation for x and work from there eg

46 (A1-122.46)/-28.643
45 (A2-122.46)/-28.643

etc. Once you put the equation in one column you can drag it down to repeat. I guess you know how to do so?

Yes I'm good for that. I know the basics just not the more advanced stuff.

I will give it a go thank you.
 
There probably is a way to take readings directly off the line but that I am not sure of. I know slightly more than your basics but not that I am afraid.
 
There's a forecast function that you can use on linear relationships. If you've got log(size) and distance data in ranges B1:B9 & C1:C9 respectively then on my old version of Excel it should be something like
=FORECAST(45, B1:B9, C1:C9)
 
Wonko's suggestion is probably best but you or others may find this useful. You can find the equation of the linear trendline without plotting the graph using these formulae:

Equation: y = mx + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
 
Back
Top Bottom