TRUNC Function

  • Thread starter Thread starter Dani
  • Start date Start date
D

Dani

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?
 
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.
 
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 formula

=TRUNC(A1) degree
=TRUNC(MOD(A1,1)*60) minute
=TRUNC(MOD(MOD(A1,1)*60,1)*60) second

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 Graesse
(e-mail address removed)

----- Dani wrote: ----

Alright, the problem I have is that I have a series of
numbers as below

115.1833
95.0000
129.8166
130.6000
110.4944
138.9055

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
95 0
129 49
130 36
110 29 4
138 54 2

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 negativ
Click to expand...
 
At least part of the problem is that you have insufficient
significant figures in you input data
115 degrees 11 minutes is 115.18333333....333... for as
many places past the decimal that you go

try something like
column A has decimal degrees
let column B be "=round(A$*3600)"
let column E be "=mod(B$,60)" seconds
let column D be "=mod((B$-E$)/60,60)" minutes
let column C be "=Trunc(A$)" degrees)

This will get you to the nearest second.
 
Back
Top