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

Raj Wrote:
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

Hi Raj

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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=481908

Raj:

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

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
significant figures regardless of the magnitude. It will even take a
number like 1234.5 and return 1230. But it won't display significant
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 with
a way of dealing with this case to make this work properly.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=481908

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


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=481908

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

"MrShorty" wrote:


Raj:

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

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
significant figures regardless of the magnitude. It will even take a
number like 1234.5 and return 1230. But it won't display significant
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 with
a way of dealing with this case to make this work properly.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=481908

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.

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

"MrShorty" wrote:


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


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=481908

http://groups.google.com/group/micro...244c8f41e91025

Jerry

Raj wrote:

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