Calc - Find delta between a series of points and a point

[lovecraft22]

Dear all,
imagine you have a serie of points with a certain (fixed) coordinate between them. I then have an additional point which might end up sitting between 2 coordinates of the above mentioned serie. What is the best way to find the delta between the serie and the point?

Here’s an example:

Serie:

x y

0 2

1 4

2 5

3 7

4 9

Point:

x y

2.5 8

In this case the result should be the delta between 8 and the interpolated value of 2.5 from the serie between 2 and 3 hence: 8-6 = 2

Of course, I don’t know a priori where the point would end up being and I cannot simply use a linear interpolation of the whole serie. What I would need is a linear interpolation of the interval of the seri (0-1, 1-2 …) and the the delta between the point and this linear interpolation.

Thanks

[Lupp]

A formula doing this in one cell is possible (and you may already have an idea), but complicated and difficult to adapt/enhance if once needed…
I would therefore want to know in what way the result shall be used. Depending on the answer a solution based on a reasonable number of helper cells, and using only rather short and well understandable formulas might be preferrabkle.
See attached example: enter code here

[lovecraft22]

Thanks. This is for budgeting reasons. Imagine you have a budget for expenses of 10k in a year. You can assume that the 10k are evenly/linearly distributed through the year so that on Jul 1st (50% of the year) your expenses should have been 5k. If on that date you have spent 5.1k instead, then you know you are 0.1k above your budget. This is easy to do. Now, imagine instead that your budget is not evenly/linearly distributed but you know how much you will spend each mont. For instance, you can assume that on January you will spend 1k, on February 0.8k etc. My point is really to understand, for this situation, how far I am on each day of the year.

[Lupp]

If a user function might be acceptable, you can try

Function interpolateForColumnsWithXascending(pX As Double, pSequenceX, pSequenceY)
REM Arrays passed via parameters by Calc are always 2D and have 1-based indices.
REM To make the function also usable for 2D arrays created differently in Basic, varibales are
REM used here for the lower bounds of the index ranges.
interpolateForColumnsWithXascending= "likeErr502"
REM If pSequenceX or pSequenceY has more than one column, only the first one is evaluated!
lX = Lbound(pSequenceX, 1) : uX = Ubound(pSequenceX, 1) : cX = LBound(pSequenceX, 2)
lY = Lbound(pSequenceY, 1) : uY = Ubound(pSequenceY, 1) : cY = LBound(pSequenceY, 2)
If NOT ((uX-lX)=(uY-lY)) Then Exit Function
If (pSequenceX(lX, cX)>pX) OR (pSequenceX(uX, cX)<pX) Then Exit Function
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
bI = lX REM basis index for interpolation
While pSequenceX(bI+1, cX)<pX
  bI = bI + 1
Wend
x0 = pSequenceX(bI, cX)
y0 = pSequenceY(lY+bI-lX, cY)
If x0=pX Then
  interpolateForColumnsWithXascending= y0
Else
  x1 = pSequenceX(bI+1, cX)
  y1 = pSequenceY(lY+bI-lX+1, cY)
  interpolateForColumnsWithXascending= y0 + (pX-x0)*(y1-y0)/(x1-x0)
End If
End Function  

//Editing 2021-03-07 about 13:10 UTC//
Application example: ask296870interpolation_1.ods

[lovecraft22]

Thanks. I’ve never built a function myself but I would assume that this is not portable, right?

[Lupp]

The code above is a LibreOffice Basic script. It’s portable (by pasting) to any Basic module whether located in a document or in a local Basic Library of your LibreOffice.
See demo attached to my asnswer above by editing. :
However, it wasn’t made specifically for the OriginalQuestion_er (@lovecraft22), but addressed the general task of interpolation in function-tables which was described as underlying.
Regarding the answer I got on my comment on the OQ, I would doubt if this is an adequate solution in the given case at all. Wouild need more information.

[anon73440385]

Hello,

assuming your

• X-values are in A2:A6
• Y-values are in B2:B6

and point (2.5 | 8) is in A9:B9 the formula could be:

=B9-(OFFSET(B2;MATCH(A9;A2:A6;1)-1;0;1;1)+(A9-OFFSET(A2;MATCH(A9;A2:A6;1)-1;0;1;1))*(SLOPE(OFFSET(B2;MATCH(A9;A2:A6;1)-1;0;2;1);OFFSET(A2;MATCH(A9;A2:A6;1)-1;0;2;1))))

See the following sample file: Q296870-DeltyByIntervalRegression.ods

Remark:
Of course @Lupp is right, that such formula is hard to maintain. Hence I’d also prefer to use some helpers columns.

[Update] - related to comment (version v2 of the sample file)

Q296870-DeltyByIntervalRegression-v2.ods

[lovecraft22]

Thanks! This does the trick.

[lovecraft22]

Thanks again for your help. A couple of additional clarifications:

1. How do I adapt this to work with rows instead of columns? I tried changing the order of the OFFSETs to
   OFFSET(A2;0;MATCH(A9;A2:A6;1)-1;1;2) but that did not do the trick…
2. How do I change the formula to consider the cumulative sum of my data to interpolate on?

Thanks again for your time

[anon73440385]

ad 1.) Changing the order is ok but you missed A2:A6 will become something like A2:E2 (X values will be in a row)

Please see version 2 of the sample file under [Update] in my answer, which implements the same thing but (X|Y) values in 2 rows (i.e. data in B26:F27)

ad 2.) I do not understand what you mean by ***cumulative sum of my data to interpolate on***

[lovecraft22]

Thanks again. This works. I had tried several times by changing everything, columns to rows included, but did not managed, not sure why.

Regarding the cumulative (or running sum): instead of doing the interpolation on the original vector (y= (2 4 5 7 9) in my opening post) I would like to do it on the running sum (y= (2 2+4 2+4+5 2+4+5+7 2+4+5+7+9)). Since I have a full matrix to do this on, I would prefer to do it in the interpolation formula you provided instead of generating a new matrix and the applying the interpolation on the latter. If this can make things easier, the length of my rows is constant.

Thanks again.