Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2005

Tip: Looking for answers? Try searching our database.

Problem with TRUNC

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steven_Archer - 23 Nov 2005 10:55 GMT
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 giv
the incorrect answer...but all the settings are the same as the on
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
Gary''s Student - 23 Nov 2005 11:11 GMT
What you are seeing is simple round-off eror.  The reason that different
things are displayed is probably due to formatting differences.
Signature

Gary's Student

> problems with TRUNC
>
[quoted text clipped - 17 lines]
>
> Any ideas? all the formulas and cell settings are identical!
Gary''s Student - 23 Nov 2005 11:12 GMT
If you only want to see an integer, then use the INT() function.
Signature

Gary''s Student

> problems with TRUNC
>
[quoted text clipped - 17 lines]
>
> Any ideas? all the formulas and cell settings are identical!
Steven_Archer - 23 Nov 2005 11:24 GMT
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

Signature

Steven_Archer

joeu2004@hotmail.com - 23 Nov 2005 12:09 GMT
> 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.
Jerry W. Lewis - 23 Nov 2005 13:30 GMT
There is a parallel discussion in
microsoft.public.excel.worksheet.functions.

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

Jerry

> problems with TRUNC
>
[quoted text clipped - 17 lines]
>
> Any ideas? all the formulas and cell settings are identical!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.