Format significant digits

[Guest]

Is there a way to apply a custom format that will only display a certain
number of significant digits?

For instance for 3 significant digits
Input Values->Display As
0.12345->0.123 or .123
1.2345->1.23
12.345->12.3
123.45->123
1234.5->1230 or 1234

I think I could make a formula that could do this, but I would rather keep
the precision. I just want to adjust the displayed precision.

 

[Niek Otten]

To a (very) limited extent this can be done in Formatting: use a Custom
format like [<0].###;[<10]0.##;00.#
This covers only the first three of your options.
Of course you could do it with IFs in a TEXT function, displaying in another
cell than where the number is.

 

[Bernard Liengme]

An advanced Google newsgroup search gave this
http://groups.google.ca/groups?as_q...981&as_maxd=4&as_maxm=1&as_maxy=2006&safe=off
best wishes

 

[MrShorty]

Here's a discussion I started that didn't get any responses. Basically
I wanted the same thing you want with the additional feature of keeping
the decimals lined up.

http://www.excelforum.com/showthread.php?t=473092

From what I've gathered, I don't think there's an easy way to do
exactly what you want. A couple of thoughts:

1) Easiest way to always get three sig figs would be to use a
scientific format (0.00E+0), but a lot of people don't want exponential
notation.
2) These days, memory and such are rather cheap. If you don't want exp
notation, then the easiest approach might be to have one table that
holds the values to their full precision, and another table for display
purposes. This table will hold a function like
=text(aa1,if(aa1<=100,"0."&rept("0",2-int(log(aa1)),"0"). That
probably needs to be tweaked to make sure it correctly handles cases
like 9.996 which will round to 10.0. Haven't got time to do all the
work for you, but that's a start.

 

[Ron Rosenfeld]

Is there a way to apply a custom format that will only display a certain
number of significant digits?

For instance for 3 significant digits
Input Values->Display As
0.12345->0.123 or .123
1.2345->1.23
12.345->12.3
123.45->123
1234.5->1230 or 1234

I think I could make a formula that could do this, but I would rather keep
the precision. I just want to adjust the displayed precision.

I don't believe it is possible when you have more than the requisite number of
significant digits to the left of the decimal. Perhaps someone else knows how,
but I don't believe that, through formatting, you can display 1234.5 as either
1230 or 1234.

Why not use your formula approach but have the actual data, with it's retained
precision, in another (possibly even a hidden) cell?


--ron

 

[Guest]

Oops your right. It should have been 1230 or 1235.

Thanks everyone for your suggestions.

 

[Ron Rosenfeld]

Oops your right. It should have been 1230 or 1235.

Thanks everyone for your suggestions.

There is still the issue of formatting with significant digits when the number
is greater than 10^sigdigits.

For example 1234.5 with three significant digits should be 1230 and I don't
know how to format it that way. While you can, with formatting, display 1235,
1235 has four significant digits, and you are specifying three.




--ron