Format for Sig-Figs

[glhansen]

When I create reports at work, I seem to be constantly adjusting the number of digits after the decimal point. What I really want is, say, three significant figures, whether they be 0.123, 1.23, 1230, or whatever. Is there a way I can tell Excel to give me as many decimal places as it takes to give three sig-figs, and fill in the rest with zeroes (e.g. 12,300 instead of 12,267)?

 

[pgc01]

What if the number is 0.12?

Hi shg

Well, if it's 0.12, the result is 0.12. I think is correct, it displays the 2 significant digits that the number has?

 

[shg]

It's correct, but it's not shown to three significant digits. I think showing a specified number significant digits intrinsically requires a string output.

Here's the formula I've used:

=TEXT(ROUND(A$1, nSig-1-INT(LOG(ABS(A$1)))), 0 & IF(nSig-INT(LOG(ABS(A$1)))>0, "." & REPT(0, nSig-1-INT(LOG(ABS(A$1)))), ""))

I expect you or Barry could abbreviate that some ...

 

[pgc01]

It's correct, but it's not shown to three significant digits.

Hi shg

I have a different interpretation on this issue.

As I understand the number has only 2 significant digits, it's not possible to show more because there are no more.

The way I see it, a significant digit carries meaning. Since the number I'm given is 0.12 I don't have any idea of what the next digit is. It may be a 0 but also a 1,2,etc.

It seems from the examples that the values are number values. This means we don't have significant trailing zeros in the decimals unless we are told beforehand what's the precision.

If the input values were text values with all digits significant, then we could have significant trailing zeros.

Ex. If we are measuring something, 0.12 and 0.120 may not be the same thing. In the case of 0.12 it may mean that we are measuring with some instrument that has a 1/100 precision, we have no idea of what the next digit will be. In the case of the 0.120 it may mean that we are measuring with some instrument that has a 1/1000 precision, the trailing zero carries as much meaning as the 1 or the 2, it says that the thousands digit is really 0.

I hope the OP clarifies this issue.
- is there a fixed precision for the values?
- are the input values number values?
- is it really significant digits we want, or, is it the number in a format that includes 3 digits after the first significant digit, even if it's necessary to pad the result with non-significant 0's.