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

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

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

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.

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
``````

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

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.

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

Thanks! This does the trick.

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?

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