Significant figures

[captbluefin]

HELP Once again I am driving myself insane.
We need to format cells in our worksheet to show significant figures.

We need to post lab results using the correct number of significan
figures.

Ex.

0.1 must be displayed as 0.100

10 needs to be displayed as 10.0

105 as 105
1023 as 1020
10343 as 10300
165435 as 165000

I have written a complicated if then else statement for every cell.
The statment works perfectly EXCEPT when a number is an exact number.

The problem is that when some of the trailing digits are 0's they ge
truncated.

We always want numbers less than 1 to show 3 decimal places
Numbers between 1 and less than 10 should show 2 decimal places.
Numbers between 10 and less than 100 should show 1 decimal place.

My statement works fine for numbers 100 and above.
My statement also works fine for numbers less than 100 when there ar
no trailing zeroes.

10.2 shows as 10.2 but 10.0 shows as 10


Please - if you are familiar with significant figures and know of
solution, I would be greatly appreciative of any hel

 

[Norman Harker]

Hi Captbluefin!

Use:

=ROUND(A1,3-1-INT(LOG10(ABS(A1))))

(from John Walkenbach's Excel 2003 Formulas)

 

[captbluefin]

Hello,

If you use the formula you gave me you will get the same result as
do:

If you put 10.0 in the cell you get 10 back - no decimal, no trailin
0

Thanks for trying.

B

 

[Harlan Grove]

Use:

=ROUND(A1,3-1-INT(LOG10(ABS(A1))))

(from John Walkenbach's Excel 2003 Formulas)

...

Then John Walkenbach needs to revise his book. Much easier to use

=--TEXT(A1,".000E+000")

or more generically where NSD stands for the number of significant digits,

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

 

[Harlan Grove]

If you use the formula you gave me you will get the same result as I
do:

If you put 10.0 in the cell you get 10 back - no decimal, no trailing
0

Oh, you want the format to adapt. You'll have to represent your values as text.

=TEXT(A1,LOOKUP(ABS(A1)+(A1=0),{0;1;10;100},{".000";"0.00";"0.0";"0"}))

I'm assuming you want 0 displayed as 0.00 (consistent with other integers)rather
than as .000. If not, remove the '+(A1=0)' term. Number formatting can't do
this.

 

[Ron Rosenfeld]

Hello,

If you use the formula you gave me you will get the same result as I
do:

If you put 10.0 in the cell you get 10 back - no decimal, no trailing
0

Thanks for trying.

BB

You will need to either format the cell after you determine the value (with an
event driven macro); or output a text string of the number formatted according
to your rules. For example:

=IF(result<1,text(result,"0.000"),if(result<10,text(result,"#.00"),if(result<100,text(result,"#.0"),text(result,"General"))))

Use Norman's formula to calculate "result" else you'll wind up nesting too many
IF's.


--ron

 

[Sandy Mann]

Oh, you want the format to adapt. You'll have to represent your values as text.

=TEXT(A1,LOOKUP(ABS(A1)+(A1=0),{0;1;10;100},{".000";"0.00";"0.0";"0"}))

I'm assuming you want 0 displayed as 0.00 (consistent with other integers)rather
than as .000. If not, remove the '+(A1=0)' term. Number formatting can't do
this.

Harlan,

With 123456 in A1 your formula returns 123456 for me when the OP wanted
123000

Would :
=TEXT(ROUND(A1,3-LEN(INT(A1))),LOOKUP(ABS(A1)+(A1=0),{0;1;10;100},{".000";"0
..00";"0.0";"0"}))

be a suitable modification?

Regards


Sandy

 

[Harlan Grove]

With 123456 in A1 your formula returns 123456 for me when the OP wanted
123000

Would :
=TEXT(ROUND(A1,3-LEN(INT(A1))),LOOKUP(ABS(A1)+(A1=0),{0;1;10;100},{".000";"0
.00";"0.0";"0"}))

be a suitable modification?

Your formula doesn't work for negative numbers. -123456 would become -120000
rather than -123000. I screwed up when I tested my earlier formula by referring
to cells containing =--TEXT(x,".000E+000"). I'd change my formula to

=TEXT(TEXT(A1,".000E+0"),LOOKUP(ABS(A1)+(A1=0),{0;1;10;100},
{".000";"0.00";"0.0";"0"}))