• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Interpolation and finding unknown value.

Peter Bartholomew

Well-Known Member
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} )
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
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
I didn't follow it but it appears to be relevant.
 

Attachments

Peter Bartholomew

Well-Known Member
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.

67595
 

Attachments

Top