significant digits for decimals

[Raj]

If I have a number 0.0246589
and would like to get the 3 significant digits, what would be the function
to use?
The function (=round("cell"3-LEN(INT("cell")))) works for numbers above zero.

Thanks in advance

 

[Paul Sheppard]

If I have a number 0.0246589
and would like to get the 3 significant digits, what would be th
function
to use?
The function (=round("cell"3-LEN(INT("cell")))) works for numbers abov
zero.

Thanks in advance

Hi Raj

If the 0.0246589 is in cell A1 in B1 put =ROUND(A1,3), this returns
vlue of 0.02

 

[MrShorty]

Raj:

Are all your values of the same magnitude (0.0xxxxx)? If so, you coul
use a 0.0000 number format or ROUND(a1,4), similar to what Pau
suggested.

If not, then the easiest way I know of to get three sig figs is to us
a scientific number format (0.00E+0), but a lot of people are eithe
uncomfortable with exponential notation or for some other reason i
isn't desirable.

A function like ROUND(A1,INT(LOG(A1)-2) will return a number to thre
significant figures regardless of the magnitude. It will even take
number like 1234.5 and return 1230. But it won't display significan
0's after the decimal point (eg a number like 0.13014 returns 0.13.
If this solution is the most desirable, I'm sure we could come up wit
a way of dealing with this case to make this work properly

 

[MrShorty]

Whoops: That function should ROUND(A1,2-INT(LOG(A1)))

 

[Raj]

MrShorty,
No, some cells will have the numbers like 236548 and some others 0.38649. I
wanted to see if there is a common function that can be used to return 3 (or
4) significant numbers.

Thanks

Raj

 

[Harlan Grove]

MrShorty wrote...
....

If not, then the easiest way I know of to get three sig figs is to use
a scientific number format (0.00E+0), but a lot of people are either
uncomfortable with exponential notation or for some other reason it
isn't desirable.

A function like ROUND(A1,INT(LOG(A1)-2) will return a number to three

....

Arguably simpler to use scientific notation as an intermediate step.

=--TEXT(A1,"0."&REPT("0",SigDig-1)&"E-0")

or hardcoded for 3 significant digits

=--TEXT(A1,"0.00E-0")

This approach also handles nonpositive numbers.

 

[Raj]

Your equation needed a slight modification. As it is, it is OK for decimals,
but not OK for big numbers. I made this modification and it works for both
magnitudes.
=ROUND(C4,(3-1)-INT(LOG(C4)))

The trick is to subtract 1 from the number of digits you want.
Thanks everybody
Raj

 

[Jerry W. Lewis]

http://groups.google.com/group/microsoft.public.excel.programming/msg/2b244c8f41e91025

Jerry