Significant figures

[Roger on Excel]

I would like a conditional format that would change the number of digits
after the decimal point depending on whether the number was in the 10's,
100's or 1000's

For example

1.02 would give 1.02
10.05 would give 10.05
100.13 would give 100.1
1004.13 would give 1004
10054 would give 10050

Can anyone help?

 

[Ron Rosenfeld]

I would like a conditional format that would change the number of digits
after the decimal point depending on whether the number was in the 10's,
100's or 1000's

For example

1.02 would give 1.02
10.05 would give 10.05
100.13 would give 100.1
1004.13 would give 1004
10054 would give 10050

Can anyone help?

I don't believe this can be done with conditional formatting. I don't know of
a format that will display 4 significant digits for values >=10000, which is
what you show.

However, it can be done using a formula, proposed by Harlan Grove in the past:

=--TEXT(A1,"."&REPT("0",SigDigits)&"E+000")

Whether you want to have this in an displayed column, or work it into an
event-triggered macro, would depend on your specific application.

A disadvantage of the formula approach is that you lose your original
precision. However, if you are only using this for display, and retaining the
original in some other cell, this might not be an issue.
--ron