Re: TRUNC Function

From: Mark Graesser (anonymous_at_discussions.microsoft.com)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 11:51:13 -0800

Dani,
.1833 times 60 equals 10.9998. No matter how many times you repeat the 3 it still comes up less than 11.

Try the following three formulas

=TRUNC(A1) degrees
=TRUNC(MOD(A1,1)*60) minutes
=TRUNC(MOD(MOD(A1,1)*60,1)*60) seconds

If you prefer you can round the seconds:
=ROUND((MOD(MOD(A1,1)*60,1)*60),)

But now you have a problem when the seconds round up to 60. You would then need to change the minute formula to:

=IF(ROUND((MOD(MOD(A1,1)*60,1)*60),)=60,ROUND((MOD(A1,1)*60),),TRUNC(MOD(A1,1)*60))
     
and to get the seconds to return 0 instead of 60, change the seconds formula to:

=IF(ROUND((MOD(MOD(A1,1)*60,1)*60),)=60,0,ROUND((MOD(MOD(A1,1)*60,1)*60),))

Hope this helps.

Good Luck,
Mark Graesser
mark_graesser@yahoo.com

     ----- Dani wrote: -----
     
     Alright, the problem I have is that I have a series of
     numbers as below:
     
     115.18333
     95.00000
     129.81667
     130.60000
     110.49444
     138.90556
     
     These are angles in decimal degrees. I want to transform
     them into degrees minutes and seconds (i.e truncate the
     value before the decimal as degrees, multiply the
     remaining decimal by 60, truncate that as minutes and
     multiply the remainder by 60 for seconds). It truncates
     the first part fine but when I try to truncate the minutes
     (after multiplication), the first one comes up as 10
     instead of 11 (I know it should be 11) unless I increase
     the precision of the truncate function. But if I do that
     then the 5th one rounds to 30 instead of 29. I need it to
     work consistently. This is the answers I should be
     getting and do using a calculator:
     
     115 11 0
     95 0 0
     129 49 0
     130 36 0
     110 29 40
     138 54 20
     
     I have tested it using a calculator and I can see how it
     would truncate to 10 (on the first one) if the number is
     not held to its full precision in the calculator memory.
     That is why I suspect it is a precision issue.
     
>-----Original Message-----
>Can you give an example?
>I only know that it rounds up when the value is negative
>>--
>>Regards,
>>Peo Sjoblom
>>>"Dani" <anonymous@discussions.microsoft.com> wrote in
     message
>news:125df01c3f64e$c5c91ec0$a001280a@phx.gbl...
>> I find that when I use the truncate function in Excel,
     it
>> sometime rounds the data up, sometimes not. In the true
>> sense it should never round the number. I suspect it
     has
>> something to do with the precision of the stored number.
>> I know I can reduce the precision of the stord data but
     I
>> was wondering if there was anyway of increasing it?
>>>.
>

Relevant Pages

  • Re: Running Watch
    ... Tom B. wrote: ... But they don't generally round to the ... nearest integer minute, they truncate to the next lower minute (i.e. ...
    (rec.running)
  • Re: Fixed Point Arithmetic
    ... adders, and whether to round or truncate? ... If the inputs ABCDE are undeterministic, then either A+B or A+C or whatever ...
    (comp.dsp)
  • RE: Rounding Problem-Please Help!!!
    ... Thank you for using the Microsoft Access Newsgroups. ... It sounds like you need to develop a function to round or truncate currency ...
    (microsoft.public.access.externaldata)
  • Re: Newbie peroblem with calculating values in cells
    ... TRUNC command would not be appropriate since it would seem from the ... now you are writing you want to ROUND the figures to two decimal places. ... Before you wrote you wanted to TRUNCate the figures to two decimal places. ... If you are recommending using the currency format in the cell, ...
    (microsoft.public.excel)
  • Re: MicroBlaze floating point precision issues
    ... implementation is presenting *supposedly* single-precision floating ... precision is 0x4036000000000000. ... So, truncate the double-precision ... cycles doing wasteful double to float conversions. ...
    (comp.arch.fpga)