hasand.blogg.se

There is also an open-source version ( link) under development. The Solver that is included in Excel is a reduced functionality version of a more extensive package from Frontline Systems. This is an unbelievably powerful tool that many folks do not even know exists. Objective Function This is the error measure that our algorithm will be minimizing. Because we need continuity, the line segments must have the same value at the breakpoint. Background Definitions Breakpoint (Knee Point) The point on the curve where the approximation transitions from one line segment to another.

For those who like to follow along, my workbook is available here. This requires that you flip the abscissa and ordinate values, which is a minor transformation.īecause the actual battery data is proprietary, I will create a piecewise-linear approximation to a discharge curve I found on the web (Figure 1). The software folks, my customers for this modeling effort, typically know V OC and want to know SOC, so they care about the SOC vs V OC curve. I normally do this task in R because I like the segmented package, however, Excel does a very good job with the task, especially if you use the Solver add-in to “tune” the model.įigure 1 is the kind of curve I normally get from our test battery folks, which is the open-circuit battery voltage (V OC) vs State of Charge (SOC). I was told to perform this task in Excel because that is the only computation tool that this customer uses. I was recently asked to create a piecewise linear model for a rather complex battery discharge curve, which is a type of task that I have performed dozens of times. Http: //newtonex celbach.wo rdpress.Figure 1: Typical Li-Ion SOC vs Voltage Curve for a specific discharge current ( Reference).

In separate tests PolynomialFit was found to be stable up to at least 50th order, with the data presented here.įor anyone interested in the Alglib routine, there is an Excel version available here: For cases where a high order polynomial is appropriate the ALGLIB PolynomialFit routine appeared to give much better results than Linest. For most interpolation purposes use of a cubic spline will normally give better results than a high order polynomial. The matrix function (at least in this case) did not give good results beyond fourth order. As noted by Lori Miller in the comments to the previous Linest post, this is probably because of changes made to the algorithm for dealing with co-linear data. The Excel Linest function and polynomial chart trendline produce different results for 6th order polynomials in the cases examined. wtonexcelb ach.wordpr ess.com/20 11/02/04/f itting-hig h-order-po lynomials/

You should be cautious about using the Excel Linest function with polynomials higher than fourth order.