How to break apart a Decimal formatted number?

[Guest]

I am trying to find a way to break up a Decimal formatted number into parts...
(Degrees to Decimal and then Decimal to Degrees)
Here's what I'm attempting...

cell F2 has 139.4916667 and I am trying to get the whole number (139) into
the adjacent cell G2. Using whole number rounding (formatted) works fine but
if the decimal places carry over.5 then it rounds up (139.50124 would be 140
not what I desire) I would like to break it apart to do the following:

cell F2 is 139.4916667
cell G2 would show the whole number 139 (even if F2 was 139.99999-no
rounding up)
cell H2 would take the remainder of the decimal portion (.4916667 and
multiply it by 60...then the whole number from this would be displayed in H2
(29.500002 would be 29 again no rounding up)
lastly in cell I2 the remainder of the last calculation .500002 would be
multiplied by 60 and entered as the number it is decimal formatted 30.00012

I have tried to use the Degrees to Decimal and the Decimal to Degrees VB
code (from MS website) for this but it requires that the user needs to enter
it into the "fx" area and I'm trying keep this worksheet in a "cell" entry
format for our personnel so they aren't confused by it (keep them in the cell
entry area not going up to the function entry text area).

Any help is always appreciated...Thanks

 

[Ron Rosenfeld]

I am trying to find a way to break up a Decimal formatted number into parts...
(Degrees to Decimal and then Decimal to Degrees)
Here's what I'm attempting...

cell F2 has 139.4916667 and I am trying to get the whole number (139) into
the adjacent cell G2. Using whole number rounding (formatted) works fine but
if the decimal places carry over.5 then it rounds up (139.50124 would be 140
not what I desire) I would like to break it apart to do the following:

cell F2 is 139.4916667
cell G2 would show the whole number 139 (even if F2 was 139.99999-no
rounding up)
cell H2 would take the remainder of the decimal portion (.4916667 and
multiply it by 60...then the whole number from this would be displayed in H2
(29.500002 would be 29 again no rounding up)
lastly in cell I2 the remainder of the last calculation .500002 would be
multiplied by 60 and entered as the number it is decimal formatted 30.00012

I have tried to use the Degrees to Decimal and the Decimal to Degrees VB
code (from MS website) for this but it requires that the user needs to enter
it into the "fx" area and I'm trying keep this worksheet in a "cell" entry
format for our personnel so they aren't confused by it (keep them in the cell
entry area not going up to the function entry text area).

Any help is always appreciated...Thanks

G2: =INT(F2)
H2: =INT(MOD(F2,1)*60)
I2 =INT(MOD(MOD(F2,1)*60,1)*60)


--ron

 

[Guest]

=ROUNDDOWN(F2,0)
=60*(F2-G2)
=60*(H2-ROUNDDOWN(H2,0))


In G2, H2, I2