A Spreadsheet That Calculates Thermistor Resistance

By changing the values of R1, R2 & R3 - the Thermostat Circuit can be adapted to operate in different areas of the temperature scale - and to provide different amounts of hysteresis.

R1 is the variable resistor. Its value determines the width of adjustment available. The greater the value of R1 - the wider the range of temperatures that may be set. However - if you make the value of R1 too high - the control will become coarse. And it will be more difficult to make precise adjustments to the temperature setting.

R2 is a fixed resistor. Its value determines the area of the temperature scale in which the thermostat will operate. The higher the value of R2 - the lower the temperatures at which the relay will activate - and vice-versa. Use R2 to take you close to your target temperature - and use R1 to provide the fine adjustment.

R3 is also a fixed resistor. Its value determines the amount of hysteresis provided by the thermostat. That is - it fixes the size of the difference - between the temperature at which the relay will energize - and the temperature at which the relay will de-energize.

Unfortunately - these three resistors do not operate entirely independently of one another. And matters are further complicated by the influences of the supply voltage - the switching voltage of the Cmos gates - and the manufacturing tolerances of the thermistor. The marked value of the thermistor can be out by as much as ą 20%.

The Zip File contains both OpenOffice and MS Works spreadsheets. Two versions of each type are available. The first - and simpler to use - spreadsheet relies on the thermistor's marked value - and the typical figure for Beta given in the datasheets. It will let you play about with the resistor values - and the supply and switching voltages. It won't be very accurate. But it will illustrate the principles involved. And - together with a little trial and error - it should help you set up your circuit.

The second spreadsheet requires much more accurate input data. You'll need to Measure The Switching Voltage of your Cmos gates. You'll also need two accurate resistance measurements - taken from your thermistor - at two different temperatures. Before using the second spreadsheet - take a look at the Thermistor Guide. It explains how you can get some real and reliable data from your thermistor.

How To Use The Simple Spreadsheet
	The input data is coloured red. You can change the values of all three resistors - to produce the temperature and hysteresis values you require. You can also change the supply voltage - the switching voltage of the gates - the value of the thermistor - and the figure for Beta. ============ R1 is half the value of the pot. I've entered 500 ohms. This represents a 1k pot - set to mid position. This position gives maximum adjustment in both directions. ============ R2 is the 3k3 resistor used in the prototype. And R3 is the 390 ohm resistor used in the prototype. ============ I also entered the measured supply voltage - which I'd set at 12v. And the theoretical switching point of the Cmos gates. That is - 6 volts - or half the supply voltage. ============ The thermistor's nominal value at 25°C - goes in F17. And Beta - taken from the datasheet - goes in E17. ============ The spreadsheet predicts the range of temperatures available with the 1k pot. The relay can be set to energize at any temperature between 25°C and 31°C. Or - the relay can be set to de-energize at any temperature between 30°C and 37°C. ============ With R1 set at 500 ohms - the hysteresis produced by the 390 ohm resistor is about 5°C. That is - the difference between the temperature at which the relay will energize - and the temperature at which it will de-energize. ============ You can change any of the figures in red - and observe the effect it has on the predicted temperatures. For example - the switching voltage of the gate is very important. And a small change in its value can produce significantly different results. Try changing E2 to 5.8 volts - and see the effect it has on the predictions. ============ The results produced by this version of the spreadsheet won't be very accurate. That's because the input data relies on typical values - rather than actual values. However - it will give you a feel for how the circuit operates - and put you in more or less the right area. Then - with a little trial and error - you can make the adjustments needed to produce the performance you require. ============

How To Use The Simple Spreadsheet

The input data is coloured red.

You can change the values of all three resistors - to produce the temperature and hysteresis values you require.

You can also change the supply voltage - the switching voltage of the gates - the value of the thermistor - and the figure for Beta.

            ============

R1 is half the value of the pot. 

I've entered 500 ohms. 

This represents a 1k pot - set to mid position.

This position gives maximum adjustment in both directions.

            ============

R2 is the 3k3 resistor used in the prototype.

And R3 is the 390 ohm resistor used in the prototype.

            ============

I also entered the measured supply voltage - which I'd set at 12v.

And the theoretical switching point of the Cmos gates.

That is - 6 volts - or half the supply voltage.

            ============

The thermistor's nominal value at 25°C - goes in F17.

And Beta - taken from the datasheet - goes in E17.

            ============

The spreadsheet predicts the range of temperatures available with the 1k pot. 

The relay can be set to energize at any temperature between 25°C and 31°C.

Or - the relay can be set to de-energize at any temperature between 30°C and 37°C.

            ============

With R1 set at 500 ohms - the hysteresis produced by the 390 ohm resistor is about 5°C.

That is - the difference between the temperature at which the relay will energize - and the temperature at which it will de-energize.

            ============

You can change any of the figures in red - and observe the effect it has on the predicted temperatures.

For example - the switching voltage of the gate is very important.

And a small change in its value can produce significantly different results.

Try changing E2 to 5.8 volts - and see the effect it has on the predictions.

            ============

The results produced by this version of the spreadsheet won't be very accurate. 

That's because the input data relies on typical values - rather than actual values.

However - it will give you a feel for how the circuit operates - and put you in more or less the right area.

Then - with a little trial and error - you can make the adjustments needed to produce the performance you require.

            ============

How To Use The More Accurate Spreadsheet
	The input data is coloured red. You can change the values of all three resistors - to produce the temperature and hysteresis values you require. You can also change the supply voltage - and the switching voltage of the gates. ============ R1 is half the value of the pot. I've entered 500 ohms. This represents a 1k pot - set to mid position. This position gives maximum adjustment in both directions. ============ R2 is the actual measured value of the 3k3 resistor used in the prototype. And R3 is the actual measured value of the 390 ohm resistor used in the prototype. ============ I entered the supply voltage - measured at 12v. And the switching voltage of my Cmos gates - measured at 6v4. ============ It's important to get this voltage right. It's the input voltage - at which the gate's output pin changes state. ============ Unfortunately - it differs from one batch of ICs to the next. And a small change in its value can produce significantly different results. Try changing E2 to 5.9 volts - and see the effect it has on the predictions. For an explanation of how to measure the switching voltage of your gates - click the link at the bottom of the page. ============ The spreadsheet also requires two accurate resistance measurements - taken from the actual thermistor you're using - at two different temperatures. Enter your temperature readings - and the corresponding resistance values - into the first four boxes of Row 17. The spreadsheet will do the rest. =========== I measured my two resistances at 16°C and 40°C. But you can choose your own two temperatures. The predictions will be at their most accurate between and around your two chosen temperatures. So choose temperatures that bracket the area of the scale you're using in your project. =========== The spreadsheet uses the figures to calculate the actual value of Beta - in the neighbourhood of the chosen temperatures. And goes on to predict how the circuit will perform with the particular set of components you're using. =========== In my example - the spreadsheet predicts the range of temperatures available with the 1k pot. The relay can be set to energize at any temperature between 20°C and 26°C. Or - the relay can be set to de-energize at any temperature between 24°C and 31°C. ============ With R1 set at 500 ohms - the hysteresis produced by the 390 ohm resistor is about 5°C - the difference between 23°C and 28°C. That is - the difference between the temperature at which the relay will energize - and the temperature at which it will de-energize. ============ I've included a prediction for the resistance at 25°C (77°F). That's the temperature at which the resistance of the thermistor -should equal its nominal colour code markings. ============ My thermistor was marked 4k7. But the measured value at 25°C was more like 4k3. Manufacturing tolerances can make the marked value of your thermistor unreliable. ============ I've also included the prediction for body temperature. If it's in the neighbourhood of your two input temperatures - you can use it to test the accuracy of your input data. Put the thermistor under your arm - and wait for the meter reading to stabilize. This may take some time. You can check the accuracy of your thermometer in the same way. ============ The results produced by this version of the spreadsheet will be more accurate. That's because the input data is real - rather than just a collection of typical values. By entering different values for R1, R2 & R3 - you can find the combination of resistors that will give you the performance you require. ============ Even so - in the real world there are many factors that will influence how the thermostat will perform. Heat energy stored in your heater may go on raising the temperature - even after the heater has switched off. Similarly - the temperature may go on falling for a time - while you're waiting for a cold heater to warm itself up. There's also the fact that the temperature may not be uniform throughout the substance you are monitoring. So expect to do a little tweaking when your circuit is in situ. ============

How To Use The More Accurate Spreadsheet

The input data is coloured red.

You can change the values of all three resistors - to produce the temperature and hysteresis values you require.

You can also change the supply voltage - and the switching voltage of the gates.

            ============

R1 is half the value of the pot. 

I've entered 500 ohms. 

This represents a 1k pot - set to mid position.

This position gives maximum adjustment in both directions.

            ============

R2 is the actual measured value of the 3k3 resistor used in the prototype.

And R3  is the actual measured value of the 390 ohm resistor used in the prototype.

            ============

I entered the supply voltage - measured at 12v.

And the switching voltage of my Cmos gates - measured at 6v4.

            ============

It's important to get this voltage right.

It's the input voltage - at which the gate's output pin changes state.

            ============

Unfortunately - it differs from one batch of ICs to the next.

And a small change in its value can produce significantly different results.

Try changing E2 to 5.9 volts - and see the effect it has on the predictions.

For an explanation of how to measure the switching voltage of your gates - click the link at the bottom of the page. 

            ============

The spreadsheet also requires two accurate resistance measurements - taken from the actual thermistor you're using - at two different temperatures.

Enter your temperature readings - and the corresponding resistance values -  into the first four boxes of Row 17.

The spreadsheet will do the rest.

            ===========

I measured my two resistances at 16°C and 40°C.

But you can choose your own two temperatures.

The predictions will be at their most accurate between and around your two chosen temperatures.

So choose temperatures that bracket the area of the scale you're using in your project.

            ===========

The spreadsheet uses the figures to calculate the actual value of Beta - in the neighbourhood of the chosen temperatures.

And goes on to predict how the circuit will perform with the particular set of components you're using.

            ===========

In my example - the spreadsheet predicts the range of temperatures available with the 1k pot. 

The relay can be set to energize at any temperature between 20°C and 26°C.

Or - the relay can be set to de-energize at any temperature between 24°C and 31°C.

            ============

With R1 set at 500 ohms - the hysteresis produced by the 390 ohm resistor is about 5°C - the difference between 23°C and 28°C.

That is - the difference between the temperature at which the relay will energize - and the temperature at which it will de-energize.

            ============

I've included a prediction for the resistance at 25°C (77°F).

That's the temperature at which the resistance of the thermistor -should equal its nominal colour code markings. 

              ============

My thermistor was marked 4k7.

But the measured value at 25°C was more like 4k3.

Manufacturing tolerances can make the marked value of your thermistor unreliable.

              ============

I've also included the prediction for body temperature. 

If it's in the neighbourhood of your two input temperatures - you can use it to test the accuracy of your input data.

Put the thermistor under your arm - and wait for the meter reading to stabilize.

This may take some time.

You can check the accuracy of your thermometer in the same way.

============

The results produced by this version of the spreadsheet will be more accurate. 

That's because the input data is real - rather than just a collection of typical values.

By entering different values for R1, R2 & R3 - you can find the combination of resistors that will give you the performance you require.

============

Even so - in the real world there are many factors that will influence how the thermostat will perform.

Heat energy stored in your heater may go on raising the temperature - even after the heater has switched off. 

Similarly - the temperature may go on falling for a time - while you're waiting for a cold heater to warm itself up.

There's also the fact that the temperature may not be uniform throughout the substance you are monitoring.

So expect to do a little tweaking when your circuit is in situ.

            ============

Guide To Choosing Resistance Values

Formulas Used In The Spreadsheets