Significant digit formatting.

[rbielaws]

Essentially, I want to format/display X number of significant digits.

For example 2 significant digits:
0.003412 -> 0.0034
341200 -> 340000
0.001 -> 0.0010
1 -> 1.0
10 -> 10

I spotted a discussion on this topic dated in '98 but it only does part
of what I want.
Being 8 years old, is there now a better way to do this part?

=ROUND(val_2b_rounded, significant_digits - 1 -
INT(LOG(val_2b_rounded)))

There doesn't seem to be a way to keep significant trailing zeros if
they are on the right of the decimal point.
If I stuck writing VB code that must be run every time an given cell
changes value, is there an easy way to kick off such a format update
function automatically?

 

[Harlan Grove]

Essentially, I want to format/display X number of significant digits.

For example 2 significant digits:
0.003412 -> 0.0034
341200 -> 340000
0.001 -> 0.0010
1 -> 1.0
10 -> 10

I spotted a discussion on this topic dated in '98 but it only does part
of what I want.
Being 8 years old, is there now a better way to do this part?

=ROUND(val_2b_rounded, significant_digits - 1 -
INT(LOG(val_2b_rounded)))

Yes, there's a better way to round to the specified number of significant
digits (nsd).

=--TEXT(val,"."&REPT("0",nsd)&"E+000")

There doesn't seem to be a way to keep significant trailing zeros if
they are on the right of the decimal point.
If I stuck writing VB code that must be run every time an given cell
changes value, is there an easy way to kick off such a format update
function automatically?

No good way to do this. You could use a Calculate event handler to iterate
through all cells containing such values and reformatting as necessary, but
you'd be better off using scientific notation.

 

[rbielaws]

Yes, there's a better way to round to the specified number of significant
digits (nsd).

=--TEXT(val,"."&REPT("0",nsd)&"E+000")


No good way to do this. You could use a Calculate event handler to iterate
through all cells containing such values and reformatting as necessary, but
you'd be better off using scientific notation.

Cool idea.
I'd never have thought of it.
I could even write a function that examined the resulting 2 parts
that come out of your formula and generates another format
string that would insure trailing zeros left of the decimal arn't lost.
Thanks.