Execution Speed: Implementing Polynomial Curve Fit Without Linest

Hi,

I have had a search on this forum and using google and wasn't able to find anything that is quite what i was looking for.

I have a VBA function that repeatedly performs a second order polynomial curve fit on some x and y values (normally 6 pairs) and then uses the relationship to interpolate a final value. This is called many tens of thousands of times in a reasonably complicated model. I beleive that this functon is the slowest part of my code and hence was looking into options to speed it up.

I was wondering whether anyone could tell me whether it would be worth imlementing this in pure VBA rather than using the LINEST and POWER functions, would this likely give a speed increase? I beleive I have the general algorithm for calculating the fit (but if anyone can point me at some pre-written code, that would be great) and I beleive it would need to calculate averages etc for the data. Should I also implement such functions in VBA? (bearing in mind the small dataset) or would the application calls be faster.

I realise that for a real speed increase I should built this function in another language and compile it as an add in/dll, however due to lockdowns in effect on the machines we use this is not a realistic option.

This might be a case of 'try it and see', but i thought I may be able tog et some sage advice and save myself some time.

Regards
Stephen

Hi thanks for your reply.

Essentially we have a simplified model that can give y values at only 6 known x values (these differ depending on the inputs given to the simplified model), we know that a second order polynomial fits all six of these values reasonably well and so it is used to perform a simple interpolation of the six values. We have validated this by running the full (and extremely slow) model and the final values predicted are well within our tolerances.

If we were ever especially sensitive to the result we would run a full model, however the fast model allows us to perform fast calculations to a degree of accuracy that is perfectly acceptable to us.

The expected response is non linear and most represents a 2nd order polynomial curve (for the bounded region we are looking at).

I hope this makes sense, please say if not.

I will take a look at implementing this entirely in code and see how well it performs.

Kind regards

Stephen

Hi,

The website was very helpful, thank you.

I just wondered if you had a similar function/info to get the 3rd order coefficients? I figure while i'm looking at it it might be a useful tool to have.

Regards
Stephen

Thank you once again, I will get reading!