I had a conversation with a customer recently who wanted to estimate the capacity and running time of their electronic systems when operating from batteries that are under various current load and temperature stresses. The battery manufacture had only specified their battery's operation under a limited number of usage scenarios-- none of which corresponded to those of my customer. To provide some guidance to this customer, I needed to do some interpolation of the battery vendor's data. This was an interesting exercise and thought it was worth sharing my work here.
Figure 1 shows the battery data that I will be interpolating. This data is from the specification here.
Normally, I use Mathcad for this kind of work. In fact, I had done this work originally in Mathcad years ago. However, this particular customer only had Excel -- sigh. After a Google search, I found an excellent VBA-based, cubic spline routine by David Braden. A little Excel and a little VBA soon gave me a spreadsheet that would interpolate Figure 1 adequately.
- Generate a linear approximation of the discharge curves on the vendor's graph
The chart shows that the "curves" are linear on a log-log plot over the range from 0.4 A to 10 A. I will do a least-square line fit over that range of currents at four temperature values: -15 °C, 0 °C, 25 °C, and -40°C. This operation will generate four slopes and intercepts.
- Use the cubic spline routine to interpolate between the slopes and intercepts from part 1 for any temperature between -15 °C and 40 °C.
This is a reasonable approach to estimating the line parameters at temperatures presented on the battery's specification sheet.
- Assign two cells for the manual input of currents and temperatures by the customer.
This allows me to compute the backup time duration of the battery for a specific set of conditions.
- Compute the effective battery capacity by multiplying the current draw by the time duration.
Many people prefer to think in terms of battery capacity and not operating time. I am just applying the definition of battery capacity to compute a value.
- Use Excel data tables to generate a chart and a couple of tables.
Excel data tables are an excellent way of generating graphical and table data for a wide range of one and two-dimensional inputs.
The Excel file that I put together is available here.
My customer is normally running his batteries at a current load of 0.8 A. Figure 2 shows the worksheet output for 0.8 A load and variable temperature.
The customer also requested data in table form. Figure 3 shows a table of discharge times versus various temperatures and current loads.
Figure 4 shows a table of effective battery capacities (A-hour) versus various temperatures and current loads.
I thought this was a good example of the use of interpolation in a real world setting. Hopefully this example will be of use to you.