Significant/Decimal digits discussion

[ChrisGreaves]

Thoughts and Ideas on Significant Digits in Excel is a good starting point.

I understand the concept of both significant digits and decimal digits.
I understand that multiplication and division takes the minimum of the significant digits for the result, and the sum of the decimal digits.
I understand that addition and subtraction takes the minimum of the decimal digits for the result, but I am unsure of the number of significant digits in the result.

I am confused, too, about so-called constants having an infinite number of significant digits. I understand that "2" can be interpreted as "2.0" or as "2.00" etc, but it seems to me that when I am told that "multiplying by 3960 will convert gallons-per-minute flow into motor horsepower requirements", that 3960 is different from 3959 and is different from 3961, so it seems to me that I have 4-digits of significance.
Likewise if I was told that the factor is "about thirty-nine sixty", I could interpret that as closer to 3960 than to 3950 or 3970, and so could assign 3-digit significance to the constant.

Discussion is invited ...

[StuartR]

3960 is not necessarily different from 3959 or from 3961.
If you expressed it as 3.96 *10³ then it would be clear that this only has 3 significant digits.

[John Gray]

Really? So if something was 3000.000 times bigger than something else, you would say that there was only 1 significant digit?

Are we not having a discussion on the distinction between accuracy and precision?

[ChrisGreaves]

3960 is not necessarily different from 3959 or from 3961. If you expressed it as 3.96 *10³ then it would be clear that this only has 3 significant digits.

I agree, and therein lies (some of) my confusion.
An engineer says to me "Multiply that by three-nine-six-oh to get horsepower", and since the engineer has vocalized four digits, I think in terms of four digits of significance.
An engineer says to me "Multiply that by three-point-nine-six times ten-to-the-three to get horsepower", and since the engineer has vocalized three digits ("three-point-nine-six"), I think in terms of three digits of significance.
In these two examples it seems to me that the expression, the form of communication dictates the number of significant digits.

Likewise, if the engineer says to me "Multiply that by three-nine-six-four to get horsepower", I think in terms of four digits of significance.
Further I think that while the value might be expressed more accurately as three-nine-six-four-point-something, whatever it is, it is closer to 3964 than it is to 3965 or 3963, so that the figure 3964 is accurate to 4 digits.

[ChrisGreaves]

Really? So if something was 3000.000 times bigger than something else, you would say that there was only 1 significant digit?

According to the web page I quoted, yes.
"The digit 0 appearing on the right of a number may or may not be a significant digit, and must be defined by the user. (1.2300 has five significant digits; 1.20 has three significant digits; and 100 could have one, two, or three significant digits--the number alone is not enough to make a determination.)"
It comes down to the expression of faith/belief/knowledge.

Are we not having a discussion on the distinction between accuracy and precision?

I'm not sure (please see "confusion", above!)
"The precision of a measurement is the size of the unit you use to make a measurement. The smaller the unit, the more precise the measurement".
In my original example, 3960 converts gallons-per-minute to horsepower, presumably still in imperial units at 550 foot-pounds-per-second. (But could be one of those new-fangled Watt thingies). The time units could be aligned "seconds") and then presumably the precision would be related to the fine degree of "gallons" and "feet".
If I used the metric "milli-litres" and "centimeters" I'd arrive at a different set of digits, maybe five in all, to replace my 3960, so 78508 (I made that up) would be more precise than 3960, and I would say I now had FIVE degrees of accuracy, but if all I've done is multiply my given 3960 by some factor, then I've fabricated precision by my use of a conversion factor - I started off with only 4 significant digits.

"The accuracy of a measurement is the difference between your measurement and the accepted correct answer. The bigger the difference, the less accurate your measurement."
In this case the key word seems to be "accepted".
If all the engineers I meet (OK, both of them), nod their heads and in unison swear that 3-9-6-0 it is, then I use their expression of faith/belief/knowledge to take it that four digits is as accurate as they care to express, and I will use 4 digits of significance in my calculations.
If they both said "use 3960.2", then I'd go with five degrees of significance.

[sdckapr]

I put all my comments in 1 post, but it encompasses several of the posts...

Thoughts and Ideas on Significant Digits in Excel is a good starting point.

I haven't seen that article in a long time...[and for the record, I am the PhD Chemist Steve Aprahamian, the article mentions]

I understand that multiplication and division takes the minimum of the significant digits for the result, and the sum of the decimal digits.

Decimal digits are immaterial to the sigfigs of mult and division. It is only based on the minimum sig figs of the terms multiplied and divided. As listed in the examples: 3.1 * 3.45678. We have 2 sig figs and 1 decimal multiplied by 6 sig figs and 5 decimals. The decimals do NOT matter, the result should have 2 sig figs (the min of 2 and 6) so the result is 11 which has NO decimals.

I understand that addition and subtraction takes the minimum of the decimal digits for the result, but I am unsure of the number of significant digits in the result.

That will depend on the sum or difference. You would have the min decimal digits + the number of "non-decimals".

I am confused, too, about so-called constants having an infinite number of significant digits.

I apologize, this is not clear in what I wrote. It really is that they SHOULD have infinite sigfigs. The point is to pick the number of sigfigs so that the number in the constant is NOT the limiting factor.

but it seems to me that when I am told that "multiplying by 3960 will convert gallons-per-minute flow into motor horsepower requirements", that 3960 is different from 3959 and is different from 3961, so it seems to me that I have 4-digits of significance.
Likewise if I was told that the factor is "about thirty-nine sixty", I could interpret that as closer to 3960 than to 3950 or 3970, and so could assign 3-digit significance to the constant.

This is exactly the problem with that zero on the end. Is it exactly 3960? The question comes into how precise is the measurement? It is at LEAST 3 sigFigs but could be 4.

Now I know that 1horsepower is 550 foot-pounds/sec (Exactly so equivalent to 550.0000000000000...). If you multiply this by 60 seconds/minute (again exact 60.000000000...) we get (exactly) and we have 33,000 foot-pounds/min.

Now comes the assumption/precision issue. A gallon of water (at sea level and 70°F) weighs 8.333.... pounds. Divide the 33,000 ft.-pounds by 8.333... pounds per gallon and we have 3960 [horsepower expressed in pump terminology]. If you are NOT at sea level or not at 70°F, then the factor will be different. [I don't do horsepower calcs, but we use nominal 8.34 lb/gal of water when doing our volume calculations for drums and this would make the factor 3957, suggesting to me that you only have 3 sig figs, the zero is NOT significant]

if something was 3000.000 times bigger than something else, you would say that there was only 1 significant digit?

With the explicit indication of 3 decimal, it suggests 7 sigfigs. If it were just 3000 it becomes less clear: it could be 1, 2,3, or 4 depending on whether the zeroes are being used as numbers or just "place-holders"... One can NOT tell from the number, it comes from the context or source of the number.

Are we not having a discussion on the distinction between accuracy and precision?

Sig Figs are a precision issue not an accuracy issue. If the bias is off enough it can appear to be an accuracy issue, but the problem stems from the lack of precision with too few sigfigs than a true accuracy issue.

Further I think that while the value might be expressed more accurately as three-nine-six-four-point-something, whatever it is, it is closer to 3964 than it is to 3965 or 3963, so that the figure 3964 is accurate to 4 digits.

This is more about the accuracy of the constant. If the true number for this occasion is 3964 and NOT 3960 than the constant has a bias, though to 3 sig figs the number is still accurate since 3960 and 3964 to 3 sig figs should give the same result...

If all the engineers I meet (OK, both of them), nod their heads and in unison swear that 3-9-6-0 it is, then I use their expression of faith/belief/knowledge to take it that four digits is as accurate as they care to express, and I will use 4 digits of significance in my calculations.

Yes. It is not only accurate but would be precise as well to those 4 sigfigs.

If they both said "use 3960.2", then I'd go with five degrees of significance.

As long as all you other numbers supported 5 sigfigs. Remember this is a constant, it should NOT be the defining determination of sigfigs. How many sigfigs is your measurement of the flow? If what you measure has 4 or more sigfigs, I would try and get more for this constant. That may even require using a 33,000 and dividing by the number of lb/gal at the actual atmospheric pressure and temperature so that you can get more than 3 sig figs I think the number suggests.

Steve

[ChrisGreaves]

I put all my comments in 1 post, but it encompasses several of the posts...

Steve, thank you very much for this response, and I am pleased to make your bit-wise (or more correctly, lot-wise!) acquaintance.
Your response is worth a tree, and I am printing it out to study it in detail, after which I shall respond in full.
I produce spreadsheets for fiscal and engineering departments, and am unsettled by the apparent disregard for "accuracy", hence my determination to assemble a few workable guidelines that can be adopted by the basic spreadsheet user.
In short I'm tired of seeing =ROUND where it need not be, and nine-figure decimal strings.

[sdckapr]

I produce spreadsheets for fiscal and engineering departments, and am unsettled by the apparent disregard for "accuracy", hence my determination to assemble a few workable guidelines that can be adopted by the basic spreadsheet user.
In short I'm tired of seeing =ROUND where it need not be, and nine-figure decimal strings.

I think you mean "precision" not "accuracy". Precision has to do with spread/range in the data. Accuracy is how close the calculation is the true value. Often accuracy can never be determined as there is no true value, just alternate methods to estimate a value.

In the attached pic I have shown a graphic depicted the 4 combinations of accuracy and precision. If a method is precise you get a tight distribution in values. If a method is accurate the average is close to the target. If a method is imprecise, it can appear inaccurate if not enough data is taken to get a good average. If a method is precise one can see quickly if it is accurate or not.

To fix accuracy often is just an adjustment, but imprecision generally requires fixing multiple areas of a process. Accuracy bias can sometimes be adjusted with a "fudge-factor", precision often can be "lived with" if one takes lots of measurements.

As to sigfigs, as I mention in the article, in most cases, one can just use all the digits in the calculations and just round to the appropriate sig figs at the end or if nothing else just using 3 is a good rule of thumb.

If you have additional questions, let me know and I will try and answer them as best that I can. Your initial query, though it had some general elements, seemed to focus on the precision of a constant. The key point with constants is to ensure that they have more significant figures than the numbers you are using or even the precision you need in the answer.

Steve

[ChrisGreaves]

I am confused, too, about so-called constants having an infinite number of significant digits.

I apologize, this is not clear in what I wrote. It really is that they SHOULD have infinite sigfigs. The point is to pick the number of sigfigs so that the number in the constant is NOT the limiting factor.

After getting distracted for a week or so I have begun to work through your advice.
I hope you can find the time to respond to a few fragmentary questions based on examples from my task at hand.
I am given a lookup table with rates for electricity.

My nifty little "lngDecDigits" function (attached) tells me that the number of digits after the decimal point vary from 0 to 5.
If I understand your earlier comments, I can treat all these values as if they had 5 decimal digits (padded to the right with zeroes).
Once I begin to use these constants in calculations, I can treat each constant as if it had 6, 7 or more digits after the decimal point, as long as they are thought of as padded to the right with zeroes.
I thing this should be a simple hurdle for me to leap, because I'm not discussing significant digits yet, nor the type of calculation.
Constants seem to be quite amenable to "fitting in" wherever they need to fit in in terms of length of digit strings.

My nifty little "lngSigDigits" function (attached) tells me how many significant digits are in the string.
The number of significant digits will be of interest to me once I start using these constant values in calculations such as addition, subtraction, multiplication and division, correct? Right now they are of academic interest only.

P.S. neither function is robust; they are "quickies" to deliver values for this set of constants.

2.JPG

[StuartR]

Chris,

Not every constant has "infinite" precision. Many calculations use constants such as the mass of an electron which are only known to a limited number of places.

[sdckapr]

Not every constant has "infinite" precision. Many calculations use constants such as the mass of an electron which are only known to a limited number of places.

I would treat an "unknown constant" similar to a variable if the precision of the number is similar to that of the other values. Typically though, the precision of these numbers is known to more sigfigs than the other measured values. The point about the "constants" is to ensure that one uses the minimum based on known precision or to a number that does not reduce the number of sigfigs based on the other measurements.

For example, One should not use 454 g/lb if the other values have more than 3 sigfigs. Use 453.59237 g/lb to ensure that the conversion value does not limit the sigfigs in the result.

Steve

[sdckapr]

Your calculation of sigfigs gave incorrect results over half the time. If the number is positive and less than 1, the first decimal digit of zero is not significant (as well as any other zeros between the decimal point and the first non-zero value). 0.08 has only 1 sig fig, 0.09947 has only 4, 0.0557 has only 3, 0.07184 has only 4, 0.09023 has only 4 and 0.099 has only 2. Any zeroes to the left of the first number are not significant.

re-examine the "rules" in the article: "The digit 0 appearing on the left of a number is only placeholder and is not significant. (0.0123 has three significant digits and 0.0000000000000000005 has only one significant digit.)"

And adding figures is not just padding with zeros. Yes if the constant is exact, that can be done (there are 12.000000.... inches/foot, but you can't take 454 g/lb and assume 454.000000000 g/lb. The correct value is 453.59237 g/lb. You have to understand where the constant comes from and expand with the actual digits in the constant. [One must also be careful of approximations. Before calculators people sometimes used to approx PI as 22/7 which is only good to 2 decimal places (3 sig figs) since 22/7 = 3.1429 and PI is 3.14159...]

Steve
[PS corrected some mistakes ...]

[ChrisGreaves]

Not every constant has "infinite" precision. Many calculations use constants such as the mass of an electron which are only known to a limited number of places.

Stuart, thanks for this insight. I'm still mulling over it.
I suspect that I will end up using various definitions depending on my role.
In the most restricted role, I am but the humble program-coder, being told what to do. If I am told to use "4.267" then I'll use exactly that and treat it as a string of four digits, with 3 after the decimal point, and there's an end to it.
In the role of program designer I might ask if that is the most accurate value available, and seek out a value with more digits in case that suits our purpose.
In the role of systems designer I might begin to classify constants as universally-inaccurate or system-defined-accurate; an example of the latter is Horsepower, which is given a man-made definition of 55 ft-lbs/sec; an example of the former was given by you, but in each case I would be able to annotate the value passed on to the program designer, and hence to the coder, so that they each understood any limitations of the string of digits.

In my current project I am given workbooks with various constant digit strings embedded in the formulas. One of my first tasks was to extract the strings and establish a series of named range values and in so doing I began to question each value: "What is 2.731? What does it do or convert? Why 2.731? is there a more accurate/precise value available?" and so on.

I mean: =+($B$13-$B$22)*2.31 and =(S9*S10)/(3960*S11) and the ever-lovely group-of-nine:

1.JPG

[ChrisGreaves]

I would treat an "unknown constant" similar to a variable if the precision of the number is similar to that of the other values.

Thanks Steve, I think I understand this.
My current data (an excerpt attached as an Excel workbook) is offered to me through a third-party. I have no control over the source. For all I know the first entry for rate (cell A2) although listed as "4" might be known to the supplier as "4.0000", but the supplier's package might truncate all trailing zero digits. Likewise cell A5 listed as "0.0557" might be known by the supplier to be "0.05570", but be output truncated. I think it is significant (grin) that in 67 rates, not one has a trailing zero.

Nonetheless I have to work with what I've got, so when I use this table of constants I am supplied with values that have anything from 1 to 5 significant digits; I don't feel that I am equipped or allowed to start making assumptions about the true values.

When I am using the third rate ("0.09947") I can be aware (in my program) that I have a value that has 4 significant digits and 5 decimal digits. I am, of course, still interested in my goal - to determine what digits to retain and /or display in intermediate results, but right now I think I see that "4 significant digits and 5 decimal digits" is an accurate (although possibly incomplete) description of the third rate. Is that correct?

How I make use of these descriptions comes later, when I get into the addition/multiplication rules.

For example, One should not use 454 g/lb if the other values have more than 3 sigfigs. Use 453.59237 g/lb to ensure that the conversion value does not limit the sigfigs in the result.

This I understand.
I have "Const_OperatingDaysPerYear=365", and can question the client as to whether they prefer "365" to "365.25" or an even more precise value.
I have "Const_nsnorm=2733.83", whatever that is, and can ask the client for improved accuracy, but in the absence of any feedback I must live with 6 significant digits and 2 decimal digits, for future reference.

This is painfully slow for me, but I realize that years and years of formatting and ROUND have preceded this conversation, and truth is I obviously didn't know what I was doing.
It's not too late to do better, I know.

[ChrisGreaves]

I would treat an "unknown constant" similar to a variable ...

This sounds to me as if I, the programmer, can classify my constants according to my knowledge of them.
If I am unable to gain any extra information about a constant, I must take it "as is", but if I can clarify the constant (your 454 lb/gal as an example), then I ought to be diligent and substitute the correct value"453.59237".
I ought, too, to document the various assumptions made about the constants, and perhaps the likely impact on calculations.

I am keeping my goal in sight - a better understanding, use of, and display of values in spreadsheets.
I figured that starting with constants would be an easy place to start.

P.S. did you mean 454 g/lb?

[sdckapr]

.S. did you mean 454 g/lb?

Yes I did. Brain-fart, I was working with densities at work at the time of the posting...

I will correct the post to have others avoid confusion...

Steve