Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to break apart a Decimal formatted number?
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 -- jgbadingerjr |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to break apart a Decimal formatted number?
On Tue, 27 Mar 2007 18:04:21 -0700, jgbadingerjr
wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to break apart a Decimal formatted number?
=ROUNDDOWN(F2,0)
=60*(F2-G2) =60*(H2-ROUNDDOWN(H2,0)) In G2, H2, I2 -- Gary''s Student gsnu200712 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to break apart a Decimal formatted number? | Excel Discussion (Misc queries) | |||
Why is a cell formatted as a zip code is showing up as a decimal? | Excel Discussion (Misc queries) | |||
Copying the number of a custom formatted number cell | Excel Discussion (Misc queries) | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
How to convert a decimal number to a non-decimal number? | Excel Worksheet Functions |