Spread sheet aided calculation for standard resistor measurement

[zlymex]

Spread sheet aided calculation for standard resistor measurement
Precise calculation is required when come to the measurement of standard resistors. The attached spread sheet will help in doing so. It also serves as a documentation for your tests. For the mathematical minded, you can look into the equations to see/verify how they calculated.

Note: when you measure an unknown resistor, you effectively compare it against a standard, whether the standard is explicitly sit there as a stand alone device, or embedded in the instrument you use. When you measure the unknown against the standard, you actually calibrate you unknown, or you transfer to the unknown. The uncertainty of the unknown will be the uncertainty of your standard 'plus' the uncertainty of your transfer/test, thus you cannot get a better uncertainty result than the standard even your transfer is perfect.

Standard resistors on their own are not ideal, there are some changing factors such as long term drift and temperature dependency. We have no ability to control the time, and not many of us have temperature stabilized lab or air bath. Variations resulting from aging and temperature must be taken into account to achieve the best results.


If your equipment is good, you have a good standard(may be borrowed), and the measurement is good, but the calculation is short or incomplete, the result will be ruined. In order to prevent that, here are some of the necessary procedures:
1. The initial calibrated data of the standard resistor must be known. There is a piece of paper inside the cover of any SR104 for instance.
2. A recent calibration is necessary in order to know the average anual drift. This also served as the most updated information for the standard.
3. Your 'measurement' is the comparison of the unknown Rx against the Standard Rs.
For example, by using an 3458A to measure the unknown, you got Rxm=1000.155 Ohm, this is not the result of Rx. You have to measure the Standard as the comparent. 
You substitute the unknown with the standard, measure again, you got Rsm=10000.123 Ohm, this is again not the result of Rs. However, the difference(Rxm-Rsm = 0.032 Ohm) is much more important, which will be used to calculate the final result of Rx.
If your standard was calibrated as 10000.002, then the Rx would be Rx=Rs+(Rxm-Rsm) = 10000.002+0.032 = 10000.034, correct?

Well, if there is no aging of the standard, and no temperature variations, it's correct. But if not, you can refer to the spread sheet for the right calculation.
4. Type in the measurement data into the spread sheet in light-green-background cells, the result will come out at yellow-background cells.


Apart from the measurement related calculations, an internal temperature calculation for SR104 is also provided.

Edit: I found out an error in the spread sheet, and upload again. If your download is before the data/time I edited the thread, please re-download. Sorry for the inconvenient.

[ManateeMafia]

Thanks for the spreadsheet. It should come in handy this summer.

[quarks]

Hello zlymex,

in your spreadsheet you have different alpha and beta values.
Are these values expected to change?
Or is this because of measurement uncertainty?

bye
quarks

[TiN]

Yay, can I steal this one too?

[zlymex]

Yay, can I steal this one too?

Sure 

[Lowpass30Hz]

Hello zlymex,

in your spreadsheet you have different alpha and beta values.
Are these values expected to change?
Or is this because of measurement uncertainty?

bye
quarks

It looks like the second set of alpha and beta values are for the Unknown.

[zlymex]

Hello zlymex,

in your spreadsheet you have different alpha and beta values.
Are these values expected to change?
Or is this because of measurement uncertainty?

bye
quarks

It looks like the second set of alpha and beta values are for the Unknown.

That is correct, Thanks.
Sorry quarks, I did not see your question yesterday.

[Lowpass30Hz]

Hello zlymex,

Thank you for this useful spreadsheet.

Your formula for finding the temperature of the SR104 only works when the sensor deviation from nominal at 23 deg C is 0%. Cell H4 is not being used in the temperature calculation.

We can find the temperature of the resistor with the following equation:

Temperature =( (R_T - R_T23 / R_T23) x 1000 + 23) deg C

Where R_T is our measured resistance value for the sensor.
R_T23 is the sensor resistance at 23 deg C. This is obtained from the percentage deviation at 23 deg C printed on the lid. R_T23= Rnominal+(Rnominal x Deviation%).

Using the formula above the calculated temperature takes the sensor deviation at 23 deg C into account. Looks like we no longer need the temperature coefficient.

Please let me know if you think I've made a mistake here.

[zlymex]

Hello zlymex,

Thank you for this useful spreadsheet.

Your formula for finding the temperature of the SR104 only works when the sensor deviation from nominal at 23 deg C is 0%. Cell H4 is not being used in the temperature calculation.

We can find the temperature of the resistor with the following equation:

Temperature =( (R_T - R_T23 / R_T23) x 1000 + 23) deg C

Where R_T is our measured resistance value for the sensor.
R_T23 is the sensor resistance at 23 deg C. This is obtained from the percentage deviation at 23 deg C printed on the lid. R_T23= Rnominal+(Rnominal x Deviation%).

Using the formula above the calculated temperature takes the sensor deviation at 23 deg C into account. Looks like we no longer need the temperature coefficient.

Please let me know if you think I've made a mistake here.

Thanks very much for that, I did miss out the cell H4 because my SR104 happened to have no deviation at 23C
I correct it and attached here(not update to the main yet).
The formula is  =H5+(H7/H3-1-H4)/H6
I hope this works also for tempco other than 0.1%/C as well
(what the SR104 manual said only works for tempco equals to 0.1%/C)

[dacman]

One problem with the example is that the definition of the US legal Ohm changed in 1990 and the 1982 value hasn't been corrected.

[zlymex]

One problem with the example is that the definition of the US legal Ohm changed in 1990 and the 1982 value hasn't been corrected.

That's right, good point, I forgot that.
Now I made a suggested calculation of the annual drift based on US Ohm adjustment in 1990 of -1.69ppm, and the drift rate of 0.053ppm per year before. The user has to input his own value of his/her choice(of the suggested value or his/her own calculation) because the adjustment of other countries were different.

[e61_phil]

For example, by using an 3458A to measure the unknown, you got Rxm=1000.155 Ohm, this is not the result of Rx. You have to measure the Standard as the comparent. 
You substitute the unknown with the standard, measure again, you got Rsm=10000.123 Ohm, this is again not the result of Rs. However, the difference(Rxm-Rsm = 0.032 Ohm) is much more important, which will be used to calculate the final result of Rx.
If your standard was calibrated as 10000.002, then the Rx would be Rx=Rs+(Rxm-Rsm) = 10000.002+0.032 = 10000.034, correct?

Well, if there is no aging of the standard, and no temperature variations, it's correct. But if not, you can refer to the spread sheet for the right calculation.

I have a question to your calculation: Why is an offset expected? I would expect a gain error in a DMM which reads another than the true value (offset should be eliminated by auto zero features). Therefore, my calculation would (in the simplest case) more like this:

Measured Standard reistance divided by calibrated values gives the gain error:

10000.123 / 10000.002 = 1.0000121

And now I would divide the result of my unknown resistor measurement by this gain error:

10000.155 / 1.0000121 = 10000.034


The result is the same due to the very small deviations, but which way is the right one?


Another way could be the measurement of the resitor ratios by the DMM and multiplication of the ratio with the known resistor.

[zlymex]

To: e61_phil
Your way is correct. My way is the simplified version of your way. That is, use addition and subtraction instead of multiplication and division.

[e61_phil]

Thank you for the clarification

[dacman]

Addition and subtraction only work because the standard is close to nominal.  If the standard had an offset of, for example, -900 ppm, then there would be an error of about 1 ppm in the result when compared to multiplying and dividing.

[zlymex]

Addition and subtraction only work because the standard is close to nominal.  If the standard had an offset of, for example, -900 ppm, then there would be an error of about 1 ppm in the result when compared to multiplying and dividing.

Correct. But if a standard has that kind of deviation as -900ppm, the tester probably won't care about 1ppm in the first place.