Unexpected Rounding on Subtraction

H

If you type in say 179.95 then in the next cell 179 and subtract the latter from the former you get 0.95.  Actually, you've got 0.949999999999989.  Maybe this is for statistical purposes (although this should just apply to relevant functions), but this is a problem, as I want to create a general formula to convert decimal values into degrees, minutes and seconds

PS I know about the formatting solution

Cheers for any hel



0
anonymous (74722)
4/30/2004 11:16:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
783 Views

Similar Articles

[PageSpeed] 25

0.95 has no exact binary representation (much as 1/3 has no exact 
decimal representation).  When you enter 179.95, computers (which do 
binary math) see not 179.95, but the closest binary approximation that 
can fit within the computer's precision.
   179.94999999999998863131622783839702606201171875
is the decimal representation of the closest binary approximation in 
IEEE double precision (used by Excel, and almost all other hardware and 
software).  What Excel gives when you subtract 179 is the exact answer 
to this approximate problem.

The simplest way to think about these issues is to note that IEEE double 
precision can approximate all 15 figure numbers, but only some 16 figure 
numbers, hence Help's "specification" that Excel's accuracy is 15 
figures.  Thus you can think of your problem as

    179.950000000000???
   -179
   -------------------
      0.950000000000???

which is consistent with what you got.

      0.949999999999989

Integers (<=15 figures) can be exactly represented, but most fractional 
values cannot.  You will have to either recast your calculations as 
strictly integer calculations, or adjust your algorithm to accommodate 
15 figure approximations to the numbers that you intend.

In this instance, you input nothing beyond 2 decimal places, and 
subtraction cannot result in anything beyond that.  Thus 
=ROUND(179.95-179,2) will hide the evidence of binary approximations 
without violence to the integrity of the calculation.  I say "hide" the 
evidence, because 0.95 will still be approximated, as revealed by 
=0.95-(1-2^-4+2^-7).

Jerry
Excel MVP

Tudor wrote:

> Hi
> 
> If you type in say 179.95 then in the next cell 179 and subtract the 

> latter from the former you get 0.95.  Actually, you've got 

> 0.949999999999989.  Maybe this is for statistical purposes (although 

> this should just apply to relevant functions), but this is a problem, 

> I want to create a general formula to convert decimal values into 

> degrees, minutes and seconds.
> 
> PS I know about the formatting solution.

0
post_a_reply (1395)
4/30/2004 11:59:36 AM
Hi!

In the example you quote, the difference between the true number an
the computed number is about 4*10^-11 (4E-11) seconds if the origina
numbers are degrees.  Something like the angle subtended at the eart
by a line 3 millionths of an inch long drawn on the surface of th
moon.

Unfortunately, I think you will find Excel won't do better. If you nee
greater accuracy, then you need something working to more than Excel'
15 d.p. Even then, decimal fractions will rarely equal binar
fractions, so there will still be "errors".

Al

--
Message posted from http://www.ExcelForum.com

0
4/30/2004 12:01:16 PM
Reply:

Similar Artilces:

Excel 2003
How can I tell Excel NOT to round up when it calculates? Click Tools menu, choose Options Choose Calculation tab Check "Precision as Displayed" "Peppermint" wrote: > How can I tell Excel NOT to round up when it calculates? Excel does *not* round up, unless you tell it to so in the formula, like with the ROUNDUP() function. It may seem that it rounds (not UP, just plain round); because no more digits fit in the column width or because you told Excel (via Formatting) not display more than a certain number of digits. "In the background" there is still an ...

Round to 2 decimal places
I want to round the calculation to 2 decimal places: eg. (215 * 3.134)/100 Any function I can use with? Have you looked at the Round() function? Alan T wrote: >I want to round the calculation to 2 decimal places: >eg. >(215 * 3.134)/100 > >Any function I can use with? -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200706/1 Alan, A slightly more complex response... Do you want to use Bankers rounding or Arithmetic rounding...

Round to million
Hi All, How do I round numbers to millions e.g. =A32,500,000 would=20 show =A32.5m Thanks for your help Regards one way would be to divide by a million -- Don Guillett SalesAid Software donaldb@281.com <anonymous@discussions.microsoft.com> wrote in message news:1f6ef01c45865$1df170c0$a601280a@phx.gbl... Hi All, How do I round numbers to millions e.g. �2,500,000 would show �2.5m Thanks for your help Regards With Arial as font...... Custom Format as ALT + 0163#.0,,"m" ALT + 0163 on the NumPad, then type in the rest. Gord Dibben Excel MVP On Tue, 22 Jun 2004 07:2...

subtracting dates to get a age
I am trying to subtract the current date from the person birth date to get a person age (in years not days). Is there a formula for this? Thanks in advance LB one way: =DATEDIF(BD, TODAY(),"y") See http://cpearson.com/excel/datedif.htm In article <9782D2DB-48FF-4D17-96FB-AB8BBF88333D@microsoft.com>, "Lori" <Lori@discussions.microsoft.com> wrote: > I am trying to subtract the current date from the person birth date to get a > person age (in years not days). Is there a formula for this? > > Thanks in advance > LB ...

Rounding
I wish to have the numbers rounded to the nearest 10 (or nearest 100 or nearest 1000. (Ex.) 1069.06 I wish to have rounded NOT to 1069 but to 1070 (the nearest 10). I can't seem to find the fix for this. Please help. =ROUND(Cell,-1) Dan E "Burgh Man" <cduncan@usgs.gov> wrote in message news:8bf701c34578$3a457b50$a401280a@phx.gbl... > I wish to have the numbers rounded to the nearest 10 (or > nearest 100 or nearest 1000. (Ex.) 1069.06 I wish to have > rounded NOT to 1069 but to 1070 (the nearest 10). I can't > seem to find the fix for this. Please help....

My 4 row formula is not ROUNDING out
Row D Row E Row F Row G $120.00 $11.67 $10.32 $185.70 120 =(D16/18)+5 =(D16/18)+3.65 =F16*18 These are my 4 rows of cells. Top row shows my results the bottom row shows my formulas. My 4th row formula is not rounding out properly. How can I get this to fix? "stacey robinson" <strobinson123@gmail.com> wrote: > Row D Row E Row F Row G > $120.00 $11.67 $10.32 $185.70 > 120 =(D16/18)+5 =(D16/18)+3.65 =F16*18 [....] > My 4th row formula is not rounding out properly. E16: =R...

Round to Thousands and x-foot the sum of the total
Round(a1,-3) will round to the thousands, Round((sum(a1:a39)),-3) will round the total to thousands but I do not know what you mean by 'x-foot' -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www.excelforum.com/showthread.php?threadid=385486 X-foot is shorthand for "cross foot", meaning the sum should be the same whether you add the elements horizontally or vertically. It's a checksum process to ensur...

Pats on the back all round
Folks, During the last few days, I've found myself asking questions in various other groups with a linux problem. The responses have been tight arsed with respect to information, or non-existant. The contrast was striking. I really thought the linux community would be more cooperative, but it's not at all. A BIG congrats to all contributors to this group, and particularly our MVP friends. Cheers, Jase ...

program terminates unexpectedly
Version: 2004 Operating System: Mac OS X 10.3 (Panther) Processor: Power PC my wife has a LOT of word documents and powerpoint presentations that she created on her computer using office for mac 2004. now whenever she tries to open one of them, the program starts up then terminates itself. if she opens the program first then tries to open the file, it attempts to open then terminates the program. if you open the program then create a new file it terminates when you try to save it. it was working one day and then it wasn't. Hmm....let's work on Word first--try the standard troublesho...

Subtracting Holiday Hours
I'm using the following expressions to calculate the number of hours between [ZeroHour] and [ActualEndDate] subtracting the Saturday and Sunday Hours. I also have a table [tblHolidays] with a [HolidayDate] field. Is it possible to create an expression that would allow me to subtract holiday hours also? Do I need to create a function? Saturdays: DateDiff("ww",[ZeroHour],[ActualEndDate],7)*24 Sundays: DateDiff("ww",[ZeroHour],[ActualEndDate],1)*24 Elapsed Time: (DateDiff("n",[ZeroHour],[ActualEndDate])/60-[Saturdays]-[Sundays]) Count the holidays betwe...

Union and Intersect range... but any SUBTRACT?
Hi all, I see the functions to union and intersect a range, but I don't see a function to subtract one range from another. Does anyone know if this function exists, or if there is a VBA function out there to do this I can paste into my application? Thanks! Ray Hi Ray, Found in a search of newsgroup archives http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100 search all words: union intersect subtract Tom Ogilvy posted a SubtractRange subroutine in a reply 2000-08-06 that should work for you. http://google.com/groups?threadm=%23OOGqH7%24%24...

Subtotal by date shows US format rather than other way round
I have a spreadsheet which has data subtotalled by date. My date format is dd/mm/yyyy, but the subtotals show mm/dd/yyyy. When I look in 'format cell' it shows my format is dd/mm/yyy, but even still it shows the other way around. How do I fix this? ...

Rounding Errors Help
Is there any way to display a rounded number in a cell, but use th original unrounded number when other cells reference that cell in calculation? It would be a pain to use the components of the rounde cell in the calculation in which i would need the unrounded number. I need a function that displays a rounded number, but uses th unrounded number in calculations -- mattflo ----------------------------------------------------------------------- mattflow's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2512 View this thread: http://www.excelforum.com/showthread.ph...

Basic example formula needed to link between sheets add/subtract
I've tired =j6+g21 but from different sheets, so its possible when i change one the other changes also. What formula do I need, because thats just not working Type the = sign, then go to the other sheet and click in J6, then hit the + sign, and go to the relevant sheet and click in G21. Hit enter. You should have a formula that looks something like =Sheet2!J6+Sheet3!G21 -- David Biddulph "gnagy84" <gnagy84@discussions.microsoft.com> wrote in message news:250E38BC-6FDF-4AE0-BB5D-EFBCA737B573@microsoft.com... > I've tired =j6+g21 but from different sheets, so i...

rounding #12
Hi, I'm having trouble with excel. I am taking information from one sheet and using formulas to have it appear on another sheet in the format I need. Right now I'm needing to take a number in this format: 12.25 12.25 2.75 9 6 6 8.75 8.75 .25 and have it appear in the other sheet in this format: 12.3 12.3 2.75 9.00 6.00 6.00 8.75 8.75 0.25 The final cells are to be right-justified, zero-filled, and kept to 4 characters including the decimal (hence the need for rounding for the 12.25 to get to 12.3) How would I do this? On 31 Jul 2006 11:41:11 -0700, "tankgirl215" <...

VBA isn't subtracting properly
Hi All, I have this small function in a worksheet (as this is where the rest of my code is written) that's just not wanting to work. It is as follows: Private Function fncIsCountingNumber(dSomeNumber As Double, dTotal _ As Double) As Boolean Dim dRoundQ As Double, dQ As Double, dDiff As Double If dSomeNumber = 0 Then fncIsCountingNumber = False Else dRoundQ = Round(dTotal / dSomeNumber, 0) dQ = dTotal / dSomeNumber dDiff = dQ - dRoundQ If dDiff = 0 Then fncIsCountingNumber = True Else fncIsC...

An unexpected error has occured
Hi, I have strange problem with one of our users. When one particular user would like to upload documents under Project Documents folder placed on PWA workspaces user recive error "An unexpected error has occured" That problem exists on different computers, when I log on on user computer and signed as my self I had no problems. That is very strange that user has that problem and I and the others do not have it. (eaven when we use user IE and only use optio Sign on as..) Do you know what may be the reason? Thank you very much for help. ...

Rounding within a range
Is it possible to analyze and round a number based on the following example: If number is between *.25 and *.74 round to *.5 If number is between *.75 and *.99 round to next higher whole #. If number is between *.01 and *.24 round to next lower whole #. Thanks -- husker ------------------------------------------------------------------------ husker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35872 View this thread: http://www.excelforum.com/showthread.php?threadid=556635 =IF(A1-TRUNC(A1)<0.25,TRUNC(A1),IF(AND(A1-TRUNC(A1)<0.75,A1-TRUNC(A1)>=0...

Rounding Problem #3
This is a typical problem where I have a column of numbers that when rounded, do not total to what you see on the screen. Rather than using the precision as displayed option in Excel, I would like to use a formula that uses the ROUND function that rounds each cell prior to summing. Is there a function or formula that would replace =(ROUND(A1,0))+(ROUND(A2,0))+(ROUND(A3,))...etc? I have tried ROUND(SUM(A1:A20),0) but this rounds after the cells have been summed. Any thoughts? Thanks in advance =SUM(ROUND(A1:A20,) entered with ctrl + shift & enter Regards, Peo Sjoblom "johnson...

Rounding Question
I have a table that has 3 elements that have been defined as Currency with 2 decimals. Everything worked in the queries perfectly. Now I have been told they no longer wish to have a decimal at all. I tried changing the definition to Double with 0 decimals. Then when I run my averaging query, I still get decimals and rounding. Ideas? Bunky In the underlying table change the currency's decimal property from Auto to 0. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Bunky" <Bunky@discussions.microsoft.com> wr...

Unexpected font size changes
Outlook 2007. When I compile an email using word 2007 as my editor I sometimes copy & past items including graphics & and text. When I check Print Preview all is well. However when I send the email there are what appesr to be random and unexpected changes/enlargements in the font sizes. This may be from 8 to 24. It is very distressing. Anyone know why this happens & what I can do about it. Cheers Judy ...

numbers being rounded
a chart that i am making has two kinds of numbers, one set is prices and another set is the codes that our system reads to apply the prices. the problem i am having has to do with the codes. Some codes have to have a .0 (as in 678.0) and some have to start with a zero (as in 054). but as soon as i click out of the cell the numbers are rounded to 687 and 54. but those zeros are important to our system. i tried to mess with the decimal places but it just didnt help. Thank you intemporal Wrote: > a chart that i am making has two kinds of numbers, one set is prices > and > ano...

How do I round up numbers when dividing?
What I am doing is in regards to money. I am dividing an amount between two people and sometimes the amount can't be divided equally so I want to be able to have the formula automatically adjust. So for instance: $5000.23 divided by 2 does not divide equally. So person A should get $2500.12 and person be should get $2500.13 The same thing goes for $523.00 Thanks. > So for instance: $5000.23 divided by 2 does not divide equally. > So person A should get $2500.12 > and person be should get $2500.13 If the total amount is in A1 and you want Person A and Pe...

Rounding a time calculation in .25 increments
Hello! I have tried a couple of rounding suggestions that I have foun somewhere in here but can't seem to get anything working. I have created a spreadsheet that calculates time differences. All o the times are in quarter hours :00, :15, :30, :45. So a calculation o 8:45 AM to 2:30 PM comes out 5.45. I need this to be 5.75. Can' figure it out although I am sure it is pretty simple. On top of that, sometimes after times are calculated, I need t subtract additional time. Say I want to take 30 minutes off so "Discounted Time" cell would have -.50 as it's value ...

Credit Card Overtendering & Rounded Up Funcionality
The control of Credit Card Overtender, It doesn't allow RMS-SO tendering amounts rounded go up. (rounded down it works well) Anybody knows how can I config RMS-SO for tendering amounts rounded up using credit cards. -- Thxs 4 ur tym EG This is a multi-part message in MIME format. ------=_NextPart_000_026B_01C96121.302C6280 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable ED, Sorry, you can't. --=20 Jeff=20 Check Point Software Search this newsgroup -- http://tinyurl.com/2lmk4w (Stolen from DRS) =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D...