Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bewilder
 
Posts: n/a
Default 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   Report Post  
Bill Martin
 
Posts: n/a
Default

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   Report Post  
JR
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem Opening an Excel File MSO9.DLL Error Mash23 Links and Linking in Excel 1 October 5th 05 06:20 AM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Excel 2003 Macro Error - Runtime error 1004 Cow Excel Discussion (Misc queries) 2 June 7th 05 01:40 PM
Run-time Error in Excel? Elena Excel Worksheet Functions 0 April 20th 05 04:26 PM
Downloading Templates for Excel error with a windows error. Lanie Moore Excel Discussion (Misc queries) 0 December 21st 04 12:39 AM


All times are GMT +1. The time now is 09:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"