+ Reply to Thread
Results 1 to 6 of 6

Problem with TRUNC

  1. #1
    Registered User
    Join Date
    11-23-2005
    Posts
    5

    Problem with TRUNC

    problems with TRUNC

    --------------------------------------------------------------------------------

    Hi,

    I seem to be having trouble with the TRUNC formula!

    In the same worksheet i have used it many times but some seem to give the incorrect answer...but all the settings are the same as the one that works!

    the formula is =TRUNC(W152/60)

    where in this case W152 is 180.

    in some of the cells the result of =TRUNC(180/60) = 3

    in the ones which are not working the result is 2.9999999999999

    Any ideas? all the formulas and cell settings are identical!

  2. #2
    Gary''s Student
    Guest

    RE: Problem with TRUNC

    What you are seeing is simple round-off eror. The reason that different
    things are displayed is probably due to formatting differences.
    --
    Gary's Student


    "Steven_Archer" wrote:

    >
    > problems with TRUNC
    >
    > --------------------------------------------------------------------------------
    >
    > Hi,
    >
    > I seem to be having trouble with the TRUNC formula!
    >
    > In the same worksheet i have used it many times but some seem to give
    > the incorrect answer...but all the settings are the same as the one
    > that works!
    >
    > the formula is =TRUNC(W152/60)
    >
    > where in this case W152 is 180.
    >
    > in some of the cells the result of =TRUNC(180/60) = 3
    >
    > in the ones which are not working the result is 2.9999999999999
    >
    > Any ideas? all the formulas and cell settings are identical!
    >
    >
    > --
    > Steven_Archer
    > ------------------------------------------------------------------------
    > Steven_Archer's Profile: http://www.excelforum.com/member.php...o&userid=29025
    > View this thread: http://www.excelforum.com/showthread...hreadid=487571
    >
    >


  3. #3
    Gary''s Student
    Guest

    RE: Problem with TRUNC

    If you only want to see an integer, then use the INT() function.
    --
    Gary''s Student


    "Steven_Archer" wrote:

    >
    > problems with TRUNC
    >
    > --------------------------------------------------------------------------------
    >
    > Hi,
    >
    > I seem to be having trouble with the TRUNC formula!
    >
    > In the same worksheet i have used it many times but some seem to give
    > the incorrect answer...but all the settings are the same as the one
    > that works!
    >
    > the formula is =TRUNC(W152/60)
    >
    > where in this case W152 is 180.
    >
    > in some of the cells the result of =TRUNC(180/60) = 3
    >
    > in the ones which are not working the result is 2.9999999999999
    >
    > Any ideas? all the formulas and cell settings are identical!
    >
    >
    > --
    > Steven_Archer
    > ------------------------------------------------------------------------
    > Steven_Archer's Profile: http://www.excelforum.com/member.php...o&userid=29025
    > View this thread: http://www.excelforum.com/showthread...hreadid=487571
    >
    >


  4. #4
    Registered User
    Join Date
    11-23-2005
    Posts
    5

    Problems

    Hi,

    Thanks for the reply.
    this doesnt seem to have solved it


    The problem is i think that the 180 figure represent minutes so what i need to do is devide this by 60 to give me the number of full hours (in this case 3).

    What both INT and TRUNC are doing is rounding the 2.999999 down to 2 - although i cant understand why it is taking 180/60 to be 2.99999 instead of 3!


    Steven

  5. #5

    Re: Problem with TRUNC

    Steven_Archer wrote:
    > the formula is =TRUNC(W152/60)
    > where in this case W152 is 180.
    > in some of the cells the result of =TRUNC(180/60) = 3
    > in the ones which are not working the result is 2.9999999999999
    > Any ideas? all the formulas and cell settings are identical!


    I suspect what you mean to say is: W152 __appears__ to be
    180. And I suspect you mean to say: some TRUNC(Wxxx/60)
    results are 3, while some other TRUNC(Wxxx/60) results are 2.99...,
    where "Wxxx" represents different cells.

    I presume that if you wrote literally =TRUNC(180/60), the results
    is 3 in every cell. Right?

    The point is: what __appears__ to be 180 in a cell is probably
    not exactly 180. If you format the Wxxx cells a Number with
    14 digits of precision, this might prove the theory.

    The "problem" is that real numbers generally cannot be stored
    exactly as they appear. This leads to annoying numerical
    "errors" of this sort. This is not an Excel problem. It is simply
    an anomaly of how (binary) computers represent real numbers
    internally.

    The "solution" is either to live with the anomaly or to work around
    it. The different results for "180" in different cells might actually
    be the answer you need. Alternatively, you could set the option
    Tools > Options > Calculation > Precision As Displayed.
    However, that can have unintended results if you are not careful
    with cell formats throughout the spreadsheet. Alternatively, you
    could use ROUND() in judicious places instead of relying on cell
    formatting to do the rounding for you.


  6. #6
    Jerry W. Lewis
    Guest

    Re: Problem with TRUNC

    There is a parallel discussion in
    microsoft.public.excel.worksheet.functions.

    Please do not separately post the same question in multiple newsgroups.

    Jerry

    Steven_Archer wrote:

    > problems with TRUNC
    >
    > --------------------------------------------------------------------------------
    >
    > Hi,
    >
    > I seem to be having trouble with the TRUNC formula!
    >
    > In the same worksheet i have used it many times but some seem to give
    > the incorrect answer...but all the settings are the same as the one
    > that works!
    >
    > the formula is =TRUNC(W152/60)
    >
    > where in this case W152 is 180.
    >
    > in some of the cells the result of =TRUNC(180/60) = 3
    >
    > in the ones which are not working the result is 2.9999999999999
    >
    > Any ideas? all the formulas and cell settings are identical!
    >
    >
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1