Find at which X the Y value is a wanted value

[Equipoise]

Hello. I've stumbled upon this place several times and found some nice tricks to make my work a bit more efficient, so I figured I could ask this here.

Is there a way to make some general formula that will search for values, pick two that are on both sides of a certain value, then (mathematically) make a linear fit based on just those two values to find out at which point X the Y value is for example 1.5?

To clarify, I may have a big table of 100 different series with for example 60 measurement points. For each series, I want to determine at which X-point the Y-value reaches 1.5. The point will differ greatly for each series and the series don't follow the exact same rules of growth (it may also happen that the Y values start to diminish again after a certain point).

Example:

Please Login or Register  to view this content.

So for series1 the wanted value would be 4.5 and for series2 it would be around 3.37. Is there a way to do this efficiently and in a copy-paste manner for all series?

[MrShorty]

For some reason, Excel does not have a built in linear interpolation function. Quattro Pro has one, and other spreadsheets might, but Excel doesn't.

If you are allowed to use UDF's, some have offered UDF's for linear interpolation (user shg comes quickly to mind). You might be able to find those offerings through this site's search engine.

If you would not be allowed to use something downloaded from the internet, it is easy enough to build linear interpolation into a spreadsheet if you break the process down into steps.

1) Locate the interval. The =MATCH() function works well
2) Return the x and y values bracketing the desired value. The INDEX() function works well, using the results obtained from the MATCH() function.
3) Calculate interpolated unknown. The =TREND() function can be used here, or you can use an algebraic expression for a straight line.

[djapigo]

Hi Equipoise,

I found this VBA code and tweaked it to your situation...
http://www.ozgrid.com/forum/showthread.php?t=64100

Check out the attachment and I show the linterp formula on row 19...

Or my formula driven interpolation with several formulas which I'm sure you came up with too...

Anyway, the VBA macro works really well...

Let us know if it doesn't work...

Dennis

re-post: re-attached the macro-enabled version after tweaking the original version

[Equipoise]

I have QuattroPro but haven't ever used it so I think it's better to stick with Excel(2010) for more fluent data handling.

Dennis, I haven't yet figured out how to modify the VBA code but I suppose keeping the files in the 'no macro' .xlsx format is better for my purposes anyway. So I gladly went with your formula driven solution though I cut some corners. I'm still working on making it as easy and efficient to copy-paste into any sheet as possible. Thank you very much for this!

[djapigo]

Sorry, Equipoise,

I can’t seem to remember to change the extension to .xlsm... I re-posted the correct version above in case you want to see it or for others to use, but if you want to stick to *.xlsx, I understand...

Glad you were able to use the other formula... tweak as needed...

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save