Scatter graphs and horizontal line intercepts

Dear All,
I am attempting to find where a horizontal line drawn across an X by Y graph intercepts the curve.

I am plotting temperature on the x axis against speed on the y axis. The results form a curve from zero to maximum, and back to zero as temperature increases.

I have an optimal rate which I would like to determine the temperature breadth of. This involves drawing a line across the function curve and determining the temperature at which it intercepts the performance curve (against the x axis "temperature").

Can anyone help? I hope my description is adequate!
Thanks!

Hi Notrealbright
Possibly better of in a math forum as you have to solve it with quadratic equati0n and linear equation

Notreal bright
didn't get it but now i do. Put all the data in a chart and attach it. It all depends on the shape of the curve/curves line/lines.

No, it was over cast and cool this afternoon.

I've attached an example file. The top row is temperature. Column 1 is speed in m/s for 10 individuals. For interests sake the data shows a lizard's running speed at these temperatures. Biologist, me. Mathematician, not me

Thankyou very much for your help with this mate, I appreciate it. I have 32 of these data sets over species and treatment temperatures.


EDIT: Sorry, realised I had omitted the averages on the bottom of the spreadsheet, have edited accordingly. I am using the average speed (m/s) from each temperature treatment. Cheers.

Dude wheres the chart?

mate,
have a look here as it may help
http://www.andypope.info/charts/intersection.htm

You can make use of the FORECAST formula.

chart has 2 additional series. First plots the datum Speed line.
2nd is a single point with 100% minus error bars.

Sadly the formula I was using above to calculate the second intercept seems not to be working in several cases. This is because the maximum speed for individuals is at different temperatures and the formula must be changed accordingly. Hmmmm...

Any ideas welcomed! I desperately need to get a line intercept for each time the horizontal line intercepts the curve.

If the drop off is always between the last 2 data points you can use the TREND formula

The complex intersect function expects the data to be in rows not columns.

If you create a table of xy values for the average and optimum lines the function will work.

The #NAME would suggest you have not enable d macros.

The Intersectcomplex is a user defined function and requires you to enable macros in order for the code to calculate the results.

You can use individual data rather than the Mean you just need to alter the ranges used.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save