Interpolation and finding unknown value.

[Adarsh S]

In the attached excel sheet I would like to find the Kinematic viscosity value for 5 and 35. How to do the interpolation i

 

[Peter Bartholomew]

This uses named formulas to identify adjacent values and calculate interpolation weights.


An alternative way of generating the chart might simply be to filter out the missing data.

 

[Adarsh S]

Thank you so much Peter. for your valuable input.

 

[Peter Bartholomew]

Sorry, the formula to pick up the temperature corresponding to the prior populated viscosity did not deal correctly with multiple consecutive blanks. Hopefully the MAXIFS formula below corrects the problem
= MAXIFS(temp, KViscosity, "<>", temp, "<="&temp)
= MINIFS(temp, KViscosity, "<>", temp, ">="&temp)


p.s. A dynamic array formula that is of interest for this task is
= XLOOKUP( temp, IF(KViscosity,temp), temp, , {-1,1} )

 

[Adarsh S]

Thank you so much Peter for your valuable input.

 

[Adarsh S]

If it is a curve type of graph, how to address this?

 

[Peter Bartholomew]

It is still possible but orders of magnitude more complicated.

I believe Excel uses Bezier curves to interpolate. I think the control points are created on a line that is parallel to the chord joining the prior and subsequent points of the curve. Both x and y are interpolated and a cubic function of a parameter that increases uniformly with node number; i.e. your sequence of temperatures 0.1,10,20,25, 30, 40 would not be seen as a sequence with missing values but, rather, a non-linear sequence to be interpolated, just as the viscosities are.

I have attached an old workbook that was created in 2011 (it came as a bit of a shock to realise that I was still using direct cell referencing at that time) which explores the construction of Bezier curves between two nodes.

p.s. A web search produced the forllowing

https://github.com/walice/beziersplines

I didn't follow it but it appears to be relevant.

 

[Adarsh S]

Thank you so much Peter for showing interest and sharing a valuable information.

 

[Peter Bartholomew]

I have had a stab at creating a Bezier interpolation within an interval selected by the user.
It gets to be a mess at the ends of the temperature range where two further values are not available.

 

[Adarsh S]

Thank you so much Peter. Its really a great effort and result and the curve is exactly as expected. Much appreciated.

 

[Peter Bartholomew]

I have improved the treatment of end segments by repeating the end node as both nearest and second nearest values. That also allowed me to get rid of the direct referencing (I always know I have gone wrong when I am forced to resort to direct cell referencing rather than range names and array formulae). I have got a better fit with Excel smoothing by moving the controls to quarter points rather than thirds. I have also implemented the calculations required to support the use of goal seek to produce a kinematic viscosity corresponding to a set temperature.

 

[Adarsh S]

Thank you very much Peter. Sorry for the late response.