Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NumberFormat for small non-zero numbers?
I have a column that subtracts two other columns and is displayed as currency. If the other two columns are "close", this column shows zero.
Most of the rows will be zero, and it's the non-zeros that are "interesting". So I've put in a NumberFormat to eliminate the zero, so it's a blank. However, in many cases the number is not zero, but some very small value... 0.0000000000123 Is there anything I can do to make these fall into the zero format as well? I could round it, but is there an easier way? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NumberFormat for small non-zero numbers?
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NumberFormat for small non-zero numbers?
wrote:
I have a column that subtracts two other columns and is displayed as currency. If the other two columns are "close", this column shows zero. Most of the rows will be zero, and it's the non-zeros that are "interesting". So I've put in a NumberFormat to eliminate the zero, so it's a blank. However, in many cases the number is not zero, but some very small value... 0.0000000000123 Is there anything I can do to make these fall into the zero format as well? I could round it, but is there an easier way? It would be prudent to explicitly round all currency calculations, even simple subtractions. For example, =ROUND(A2-A1,2). That avoids infinitesimal anomalies that arise in Excel arithmetic due to the way that Excel represents numbers internally (64-bit binary floating-point). For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But IF(ROUND(10.1-10,2)=0.1,TRUE) returns TRUE as expected. But if you wish, you might use the following Custom format: [<0.005]"";$#,##0.00 Note that that works only for non-negative currency values. The following Custom will also display negative currency values properly: [<=-0.005]-$#,##0.00;[<0.005]"";$#,##0.00 However, both Custom formats display "-" (without quotes) for negative values that are intended to displayed as just the null string. (A defect, IMHO.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells print so small I cannot read numbers. How do I fix? | New Users to Excel | |||
Sorting numbers in a row from small to large | Excel Discussion (Misc queries) | |||
using linest with very small numbers | Excel Worksheet Functions | |||
setting numberformat for -ve numbers | Excel Programming | |||
Small font on row & column numbers - wierd | Excel Discussion (Misc queries) |