A better approach to designing an RTD interface with a spreadsheet
An improved Design Idea directly calculates component values for an RTD circuit using an Excel spreadsheet.
Aubrey Kagan, Emphatec, Markham, ON, Canada; Edited by Martin Rowe and Fran Granville -- EDN, September 18, 2008
An earlier Design Idea described how to linearize the output of an RTD (resistance-temperature-detector) sensor and how to calculate the resistor values using a spreadsheet (Reference 1). That idea limited the use of Microsoft Excel to calculating the coefficients you need for the polynomial expression and stopped short of using Excel to calculate the resistor values. You can generalize this proposed approach such that you can select any type of RTD and any temperature range, but this Design Idea limits the details to the following example.
You can download the worksheet (Figure 1) by clicking here. You plot the chart as an XY diagram, and you create the trend line on the chart using a second-order polynomial, which will appear on the chart. The original Design Idea included this information. Unfortunately, you cannot access the coefficients you generate in this way from the worksheet, so you cannot directly calculate the resistor values.
To access the polynomial coefficients, you can use Excel’s LINEST array formula. It prescribes a specific way of entering data; without that protocol, Excel will not provide the desired results. LINEST returns a number of regression statistics; to allow for these statistics, you must first highlight the range on the worksheet on which you want the regression results. Only the polynomial coefficients are important in this example, so this Design Idea limits the returned results by selecting block B24:D24 for those three values. You then enter the following line into the formula bar at the top of the worksheet:=LINEST(G5:G21,E5:F21,,TRUE).
Simultaneously press the Control, Shift, and Enter keys rather than just Enter to terminate this command. The coefficients will then drop into the selected range. Excel will add the braces, { }, to indicate the array formula. The input range of the function in the formula above includes the Vt2 column, allowing LINEST to create a second-order polynomial equation.
You can enter user-selected values as set numbers, providing easy and quick modification and an immediate update of the calculated values. These values include the current source through the RTD, the reference voltage, and the value of R7 and R9, all of which are “named” cells that the formulas refer to. The idea rewrites the original formulas to isolate the desired variable. You will find each in the associated cells for R6, R8, and R10 on the worksheet. You could also complete the model by creating an automatic look-up of standard resistor values (Reference 2).
| References |
|
