# Trouble interpolating data on semi-logarithmic chart

1. ## Trouble interpolating data on semi-logarithmic chart

Hi guys,

I hope this is in the right spot.

I have a set of data and I'm trying to return a value where y = 30. This is really easy for a linear or polynomial set but this data is plotted on a semi-logarithmic chart with straight lines in between points. I have attached an Excel file, because it's a bit difficult to explain.

You can see in both graphs, using either INDEX/LINEST or FORECAST gives slightly the wrong result. Both formulas look only at the data point above and the data point below 30 (Y) but I'm getting 4.06 and 3.95. I can see that X should equal about 3.65.

As I said; the spreadsheet should better illustrate what I'm getting at.

Can anyone help?

Many thanks all,

Mick

2. ## Re: Trouble interpolating data on semi-logarithmic chart

Is that 3.65 an eyeballed number, or can you explain you came up with that?

The 3.95 appears to come from linear interpolation using y=mx+b
The 4.06 appears to come from linear interpolation using ln(y)=mx+b
Perhaps you are looking for linear interpolation using y=ln(x)+b (which gives me 3.76).

I expect that, if you figure out exactly what kind of equation to use for the interpolation, you will get the answer you want. At this point, it looks to me like you need to figure out what kind of equation is the best one to use for the kind of data you have.

3. ## Re: Trouble interpolating data on semi-logarithmic chart

Hi MrShorty,

3.65 in indeed an eyeballed number.

Originally Posted by MrShorty
using y=ln(x)+b (which gives me 3.76)
Manually entering 3.76 looks like the right number on my chart, but I don't quite get how you reached this number. Is b calculated using INTERCEPT?

Mick

4. ## Re: Trouble interpolating data on semi-logarithmic chart

I basically used the same equations you did, though I often get rid of the functions and just spell it out.

3.95 =FORECAST(F12,A13:A14,B13:B14) -- basic linear interpolation with no transformation

4.06 = INDEX(LINEST(A13:A14,LN(B13:B14)),1)*LN(E12))+INDEX(LINEST(A13:A14,LN(B13:B14)),1,2) -- basic linear interpolation, but transforming y to be LN(y). Usually, rather than using two instances of the LINEST() function to return m and b, I will usually simply use a point-slope form of the equation of a line (ln(y2)-ln(y1))/(x2-x1)=(ln(y3)-ln(y1))/(x3-x1) fill in the known variable and solve for the desired unkown.

3.75 is similar to 4.06, only applying the =LN() transformation to the x values instead of the y values (y2-y1)/(ln(x2)-ln(x1))=(y3-y1)/(ln(x3)-ln(x1)).

So, while I could have used the INTERCEPT() function had I wanted to, I rarely use the INTERCEPT() function.

5. ## Re: Trouble interpolating data on semi-logarithmic chart

Perfect! Thank you very much Shorty.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1