significant figures problem

[BAlGaInTl]

Hello all...

I'm using a formula that I found elsewhere on the board to rount a number based on significant figures. This is a sample of what I'm using.

ROUND(G32,B11-1-INT(LOG10(ABS(G32))))

Where G32 is the number you want to round, and B11 is the number of sig. figs. desired.

Here is the problem that I have found. It seems that Excel insists on dropping the last number if it is a zero. So, in effect, what you get is not correct if the last significant figure is a zero.

Example. Lets say that I want 2 significant figures.

0.00123 will correctly display as 0.0012

However,

0.00101 will display as 0.001, which is only 1 significant figure, and not 2.

This only happens when the last number is a zero.

Help?

 

[amigos]

try to set up fixed number of decimal places,
that might help you

 

[BAlGaInTl]

That would kind of defeat the use of significant figures in this case.

Different people will need a different number of decimal places, but normally only 2 or 3 significant figures.

For instance...

Someone may be get the result
0.0000123456789

0.000012 would be the reported result ( 2 sig figs)

Another person doing a different method may get a result of
0.00123456789

0.0012 (2 sig figs)

If I limit it to the number of decimal places and match the first answer, the second answer becomes

0.001234 which is bad since the 3 and 4 are not significant or certain.

I'm a bit surprised that excel has no way of dealing with significant figures really.

 

[Andrew Poulsom]

Excel always drops trailing zeroes. You will need to format the cell to show them, eg in your first example format the cell as 0.0000.

 

[tusharm]

You are joking, right? What's the mathematical difference between 0.001 and 0.0010? AFAIK, zero is not a significant digit after the decimal place!

Check the result of your formula. It is 0.001 and not 0.0010. How would XL know it should not show the number as 0.00100000000000000000000000000000000000?

Now, if XL dropped the zero to the left of the decimal, it might be cause for concern!

Yes, it would be nice if XL had some native capability to handle significant digits.

Finally, I believe someone has pointed out some limitation(s) to the use of the LOG10() function in this context. You might find more if you search the google.com archives of the XL newsgroups.

{snip}
However,

0.00101 will display as 0.001, which is only 1 significant figure, and not 2.

This only happens when the last number is a zero.

Help?

 

[BAlGaInTl]

You are joking, right? What's the mathematical difference between 0.001 and 0.0010? AFAIK, zero is not a significant digit after the decimal place!

Yes, it is significant. Many people often abuse significant figures and rounding. Here is the lowdown on zeros...

Leading zeros prior to the decimal may or may not be significant (normally not)
Leading zeros after the decimal are never significant 0.001 (1 sig fig)
Zeros within a number are always significant 1.001 (4 sig figs)
Trailing Zeros after the decimal place are always significant 0.0100 (3 sig figs)

The general rule of significant figures is that you should always report all certain digits, and one that is uncertain.

So, if you have a balance that measures 0.1234 (four decimal places). The error in that balance should only be in the last number (4).

By reporting the number as 0.1234, I'm saying that I know for sure that 0.123 is correct, and there may be some error in the 4. Follow?

By using that same argument...

If I report 0.10, I'm saying that I know for sure that 0.1 is true, but that there may be some error in the trailing 0. I have 2 significant figures.

However, if excel drops that trailing zero and reports 0.1, I'm saying that I'm certain of nothing. And while this may be true in life, it is not true of my reported value.

Finally, if you follow the rules of significant figures, there is a mathematical difference as well.

1.234 x 1.2 = 1.5
1.234 x 1.20 = 1.48

I used to have the hardest time trying to get my students to understand this when I was a teacher.

 

[tusharm]

I have no desire to get into an argument over this, but this is the first time I've come across this 'significance' -- and most people would consider my academic, consulting, and other work experience mind-numbing.

In all this time I have *never* run across this rule. Which leads me to conclude that what you mention is, at best, a convention, though hardly an universal one.

Even if one were to accept the convention you mention, in the example you quote (1.234 * 1.2 vs. * 1.20), why is the number of digits in the 2nd number more important than the number of digits in the first number? Given that 1.234 * 1.2 is *exactly* 1.4808, I would think the 3 decimal places in the first number would require the result rounded to 1.481.

{sniip}
Yes, it is significant. Many people often abuse significant figures and rounding. Here is the lowdown on zeros...
{snip}Finally, if you follow the rules of significant figures, there is a mathematical difference as well.

1.234 x 1.2 = 1.5
1.234 x 1.20 = 1.48

I used to have the hardest time trying to get my students to understand this when I was a teacher.

 

[goblin]

Well, Tusharm, the speed of light is 299.792,458 km/s.

Find a stopwatch, start it, and try to stop it after 1 second. You only get one try.

How far would you say light has travelled in the time you measured? Let us say you are pretty good with a stop watch, so the time you measured is 1,0 seconds. I.e. two significant figures. But you have 9 significant figures in the speed of light! Are you going to say that light travelled 299.792,46 km in that second you measured?

You should say that light travelled about 300000 km in that second, which is the speed of light with not quite so many significant figures.

The reason for this is that the error in the measurement lies in the first decimal place, so if you had measured 0,1 seconds too long, light would have travelled about 30000 km in that time. So you aren't very certain how far the light has travelled. It might be 300000 km, might be 330000 km, might be 270000 km. But reporting it as 299792 km is just plain lying. It implies precision in your time measurement that just wasn't there!

 

[indiantrix]

Why not, instead of describing a value as 1.230 , describe it as
1230/1000. Then your significant digits will be removed from the right side of the decimal and will be treated as whole numbers, subsequently to be divided by the correct divisor? Maybe I oversimplify...and perhaps I lack an understanding, but this seems to circumvent the problem. /s/ Larry

 

[tusharm]

Like I wrote in my previous response, I don't intend getting into arguments on this subject. So, this is my last post on this subject (other than a discussion on the subject of the last paragraph).

I *know* more than I want to know about approximation, rounding, and making recommendations to executives of Fortune 50 companies, not to mention investing my own money based on the analysis; so, I *don't* need abstract lectures on the subject.

Nor do I understand the relevance of your example. If we know the speed of light to 2 decimal places and we want to know how far it traveled in 1 second, we don't need to measure it. If you refer to the previous discussion, we *know* the exact result of multiplying 1.234 by 1.2 is 1.4808. The only question is why report it to 1 decimal place rather than three decimal places. On the other hand, if you don't know the speed of light, and you measure it with a stopwatch, and you accept that you will live with that estimate, then the value you have is the *best* statistical estimator of the actual variable. So, in no case would you introduce a fudge factor!

A practical example I use to illustrate my thinking about approximation and rounding is this. For those who know how a president is elected in the U.S., what is the minimum percentage of popular votes required to become president? There's an exact answer that requires a fair amount of analysis in Excel and there's an approximate answer that I can give you in 1 second. The two are not that far apart. [Given that the current president had less popular votes than his opponent, the answer is not 50% or any number greater than that. {g}]

Well, Tusharm, the speed of light is 299.792,458 km/s.

Find a stopwatch, start it, and try to stop it after 1 second. You only get one try.

{snip}