Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format significant digits
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format significant digits
An advanced Google newsgroup search gave this
http://groups.google.ca/groups?as_q=... 006&safe=off best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sloth" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format significant digits
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. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=498048 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format significant digits
On Wed, 4 Jan 2006 12:00:04 -0800, "Sloth"
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format significant digits
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" 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format significant digits
On Thu, 5 Jan 2006 08:01:02 -0800, "Sloth"
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
why does currency format change to number format? | Excel Discussion (Misc queries) | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) | |||
How to format a date to a different format | Excel Discussion (Misc queries) |