Home |
Search |
Today's Posts |
#1
|
|||
|
|||
excel floating digits error
I have created a chart with 2 columns of Nos. and a third column with the
results from the subtraction of column 1 from 2. At the bottom of each colum I also have the resultant of the column. the display is to 0 digits but the input on each box is to 2 digits. Also I have selected the numerical to show in Red and parenthesis if it is a negative No. The final resultant at the bottom of column 3 should equal 0. I cannot round to display because I would losse the 2 digit accuracy on the input boxes. The problem that I'm having is that the final resultant shows a negative 0. If I extend the final result to show max. digits to see why is a negative 0, somewhere around the 20th. digit numbers starts to show up instead of 0. Where do these numbers come from? I only inputed to 2 digits, everything else should be 0. |
#2
|
|||
|
|||
Bewilder wrote:
I have created a chart with 2 columns of Nos. and a third column with the results from the subtraction of column 1 from 2. At the bottom of each colum I also have the resultant of the column. the display is to 0 digits but the input on each box is to 2 digits. Also I have selected the numerical to show in Red and parenthesis if it is a negative No. The final resultant at the bottom of column 3 should equal 0. I cannot round to display because I would losse the 2 digit accuracy on the input boxes. The problem that I'm having is that the final resultant shows a negative 0. If I extend the final result to show max. digits to see why is a negative 0, somewhere around the 20th. digit numbers starts to show up instead of 0. Where do these numbers come from? I only inputed to 2 digits, everything else should be 0. --------------------- It's normal system rounding. Excel (and pretty much all other general software) uses IEEE standard arithmetic which only carries about 14 digits. Doing the difference of two close numbers brings the issue to the fore. One solution is to replace your: [ ] = B1 - A1 With: [ ] = if(abs(B1-A1) < 0.000000001, 0, B1-A1) Good luck... Bill |
#3
|
|||
|
|||
Try this at the bottom of your column...
example... If(sum(C1:C20)<0,0,sum(C1:C20)) If the sum of your column is less than zero, it will now display zero, not negative zero. "Bewilder" wrote: I have created a chart with 2 columns of Nos. and a third column with the results from the subtraction of column 1 from 2. At the bottom of each colum I also have the resultant of the column. the display is to 0 digits but the input on each box is to 2 digits. Also I have selected the numerical to show in Red and parenthesis if it is a negative No. The final resultant at the bottom of column 3 should equal 0. I cannot round to display because I would losse the 2 digit accuracy on the input boxes. The problem that I'm having is that the final resultant shows a negative 0. If I extend the final result to show max. digits to see why is a negative 0, somewhere around the 20th. digit numbers starts to show up instead of 0. Where do these numbers come from? I only inputed to 2 digits, everything else should be 0. |
#4
|
|||
|
|||
JR wrote:
Try this at the bottom of your column... If(sum(C1:C20)<0,0,sum(C1:C20)) Better: MIN(0,SUM(C1:C20)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem Opening an Excel File MSO9.DLL Error | Links and Linking in Excel | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Run-time Error in Excel? | Excel Worksheet Functions | |||
Downloading Templates for Excel error with a windows error. | Excel Discussion (Misc queries) |