Excel gurus

[sven256]

I'm not the best with excel and i'm wondering if there is a simple way to do this

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.

 

[Snookums]

Surely just input into the equation?

 

[sven256]

Surely just input into the equation?

Any idea how to?

As stated "I'm not the best with excel"

 

[hyperseven]

What data do you want on the worksheet? You have bot stated what you are trying to achieve

It gives you the formula on that graph.

 

[sven256]

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).

 

[Snookums]

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?

 

[sven256]

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.

 

[Snookums]

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.

 

[wonko]

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)

 

[Grrrrr]

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)