Format significant digits

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.

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.

--
Kind regards,

Niek Otten

"Sloth" <Sloth@discussions.microsoft.com> wrote in message
news:DCEBD6DC-DB91-416F-87D0-DA3099405877@microsoft.com...
> 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.

An advanced Google newsgroup search gave this
http://groups.google.ca/groups?as_q=...=2006&safe=off
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sloth" <Sloth@discussions.microsoft.com> wrote in message
news:DCEBD6DC-DB91-416F-87D0-DA3099405877@microsoft.com...
> 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.

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.

On Wed, 4 Jan 2006 12:00:04 -0800, "Sloth" <Sloth@discussions.microsoft.com>
wrote:

>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

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

Thanks everyone for your suggestions.

"Ron Rosenfeld" wrote:

> On Wed, 4 Jan 2006 12:00:04 -0800, "Sloth" <Sloth@discussions.microsoft.com>
> wrote:
>
> >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
>

On Thu, 5 Jan 2006 08:01:02 -0800, "Sloth" <Sloth@discussions.microsoft.com>
wrote:

>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

You could try this =IF(INT(A1)>0,ROUND(A1,3-LEN(INT(A1))),ROUND(A1,3)) where A1 represents the cell with your number, and the 3s represents the significant digits. You can replace ROUND with ROUNDDOWN if that is the way you wish to go.