Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Is there a way to separate and use the numbers, to the left of and to the
right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°1710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Hi,
Try this to convert decimal degrees to deg, mins & seconds Alt +F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste this UDF in call with =ConvDeg(a1) Function ConvDeg(DecAngle) As Variant Degs = Int(DecAngle) Mins = (DecAngle - Degs) * 60 secs = Format(((Mins - Int(Mins)) * 60), "0") ConvDeg = " " & Degs & "° " & Int(Mins) & "' " & secs & """" End Function Mike "Doc" wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°1710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Ah you wanted the output in 3 different cells and sadly a function cant do
that so 3 seperate formula Degrees =INT(A1) Minutes =INT((A1-INT(A1))*60) seconds =((A1-INT(A1))*60)-INT((A1-INT(A1))*60) Mike "Mike H" wrote: Hi, Try this to convert decimal degrees to deg, mins & seconds Alt +F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste this UDF in call with =ConvDeg(a1) Function ConvDeg(DecAngle) As Variant Degs = Int(DecAngle) Mins = (DecAngle - Degs) * 60 secs = Format(((Mins - Int(Mins)) * 60), "0") ConvDeg = " " & Degs & "° " & Int(Mins) & "' " & secs & """" End Function Mike "Doc" wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°1710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
One way:
A1: 27.286 if you're interested in numbers: B1: =INT(A1) -- 27 C1: =MINUTE(A1/24) -- 17 D1: =SECOND(A1/24) -- 10 or if you're just interested in display: B1: TEXT(A1/24,"[hh]°") -- 27° C1: =MID(TEXT(A1/24,"h:mm'"),3,3) -- 17' D1: =TEXT(A1/24,"ss\""") -- 10" In article , Doc wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°1710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
You don't need to use separators. If your decimal degrees (e.g,
27.5111 = 27 deg, 30 min, 40 sec) is in D5, you can use =INT(D5) to get the degrees =MINUTE(D5/24) to get minutes =SECOND(D5/24) to get seconds If you really want to separate the integral and fractional portions of D5 into separate cells with a single formula, use the following array formula: ={1,0}*INT(D5)+{0,-1}*(INT(D5)-D5) Note the difference between the parens () and the curly braces {}. Select the cells you want the integral and fractional portions to go in, type the formula above and press CTRL SHIFT ENTER instead of ENTER. Since this is an Array Formula, you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces - Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. You can also use two separate formulas to get the integral and fractional portions. =INT(D5) returns the integral portion =D5-INT(D5) returns the fractional portion Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 17 Jan 2009 13:07:01 -0800, Doc wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286), to the older form of degrees, minutes and seconds (i.e. 271710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Hmm,
I seem to have made very hard work of something you made very simple. Must try harder!! Mike "JE McGimpsey" wrote: One way: A1: 27.286 if you're interested in numbers: B1: =INT(A1) -- 27 C1: =MINUTE(A1/24) -- 17 D1: =SECOND(A1/24) -- 10 or if you're just interested in display: B1: TEXT(A1/24,"[hh]°") -- 27° C1: =MID(TEXT(A1/24,"h:mm'"),3,3) -- 17' D1: =TEXT(A1/24,"ss\""") -- 10" In article , Doc wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°17â10â), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnât require the SEPARATION of a number. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
In the interest of completeness, you can use the following array
formula in 3 cells to return the degrees, minutes, and seconds to those cells. Select the 3 cells that should contain the results, type the formula ={1,0,0}*INT(D5)+{0,1,0}*MINUTE(D5/24)+{0,0,1}*SECOND(D5/24) and press CTRL SHIFT ENTER. The cells will contain the degrees, minutes, and seconds of the decimal degrees value in D5. This assumes that the 3 cells are all on the same line. If they are all in the same column, over 3 rows, use =TRANSPOSE({1,0,0}*INT(D5)+{0,1,0}*MINUTE(D5/24)+{0,0,1}*SECOND(D5/24)) Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 17 Jan 2009 13:07:01 -0800, Doc wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286), to the older form of degrees, minutes and seconds (i.e. 271710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Thanks Mike,
I tried but never could get it to work. I kept getting a "compile error". Probably something I did wrong, I know ABSOUUTELY NOTHING about writing OR running VB modules. I did, in the mean time figure out how to do it with the integer function. Thanks. Doc "Mike H" wrote: Hi, Try this to convert decimal degrees to deg, mins & seconds Alt +F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste this UDF in call with =ConvDeg(a1) Function ConvDeg(DecAngle) As Variant Degs = Int(DecAngle) Mins = (DecAngle - Degs) * 60 secs = Format(((Mins - Int(Mins)) * 60), "0") ConvDeg = " " & Degs & "° " & Int(Mins) & "' " & secs & """" End Function Mike "Doc" wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°1710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
It worked GREAT.
Thanks for your help. Doc "JE McGimpsey" wrote: One way: A1: 27.286 if you're interested in numbers: B1: =INT(A1) -- 27 C1: =MINUTE(A1/24) -- 17 D1: =SECOND(A1/24) -- 10 or if you're just interested in display: B1: TEXT(A1/24,"[hh]°") -- 27° C1: =MID(TEXT(A1/24,"h:mm'"),3,3) -- 17' D1: =TEXT(A1/24,"ss\""") -- 10" In article , Doc wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°17â10â), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnât require the SEPARATION of a number. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Thanks Chip,
You guys are all great! I'm curious though, where did you come up with the "24" for the divisor?? Doc "Chip Pearson" wrote: You don't need to use separators. If your decimal degrees (e.g, 27.5111 = 27 deg, 30 min, 40 sec) is in D5, you can use =INT(D5) to get the degrees =MINUTE(D5/24) to get minutes =SECOND(D5/24) to get seconds If you really want to separate the integral and fractional portions of D5 into separate cells with a single formula, use the following array formula: ={1,0}*INT(D5)+{0,-1}*(INT(D5)-D5) Note the difference between the parens () and the curly braces {}. Select the cells you want the integral and fractional portions to go in, type the formula above and press CTRL SHIFT ENTER instead of ENTER. Since this is an Array Formula, you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces - Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. You can also use two separate formulas to get the integral and fractional portions. =INT(D5) returns the integral portion =D5-INT(D5) returns the fractional portion Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 17 Jan 2009 13:07:01 -0800, Doc wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286°), to the older form of degrees, minutes and seconds (i.e. 27°1710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75,
etc. In article , Doc wrote: I'm curious though, where did you come up with the "24" for the divisor?? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Sorry JE, but I still don't follow. If .5 is noon and .75 equals 6:00 pm,
wouldn't that mean that 24 would be 24 days?? Doc "JE McGimpsey" wrote: XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75, etc. In article , Doc wrote: I'm curious though, where did you come up with the "24" for the divisor?? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Yes, 24 would be 24 days, but when you divide the integer hours (or
their analogue, degrees, in this case), you get a value that XL can interpret as a time. 24/24 = 1.0 = 24:00 (midnight) and 12/24 = 0.5 = 12:00 noon. Your data was in the form 27.286. Since degrees are divisible into minutes and seconds the same way hours are, you can divide that by 24 to get 27.386/24-th of a day. You can then tell XL to display it as [h]:mm:ss, or pass the value on to MINUTE(), SECOND(), etc. In article , Doc wrote: Sorry JE, but I still don't follow. If .5 is noon and .75 equals 6:00 pm, wouldn't that mean that 24 would be 24 days?? Doc "JE McGimpsey" wrote: XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75, etc. In article , Doc wrote: I'm curious though, where did you come up with the "24" for the divisor?? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
Thanks JE, it makes a lot more sense now!
I appreciate all of your help. Doc "JE McGimpsey" wrote: Yes, 24 would be 24 days, but when you divide the integer hours (or their analogue, degrees, in this case), you get a value that XL can interpret as a time. 24/24 = 1.0 = 24:00 (midnight) and 12/24 = 0.5 = 12:00 noon. Your data was in the form 27.286. Since degrees are divisible into minutes and seconds the same way hours are, you can divide that by 24 to get 27.386/24-th of a day. You can then tell XL to display it as [h]:mm:ss, or pass the value on to MINUTE(), SECOND(), etc. In article , Doc wrote: Sorry JE, but I still don't follow. If .5 is noon and .75 equals 6:00 pm, wouldn't that mean that 24 would be 24 days?? Doc "JE McGimpsey" wrote: XL stores times as fractional days, so 12:00 noon =0.5, 6:00 pm = 0.75, etc. In article , Doc wrote: I'm curious though, where did you come up with the "24" for the divisor?? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimal numbers in Excel
You might also be interested in www.cpearson.com/Excel/LatLong.aspx for formulas that work with latitude and longitude and Great Circle Distances. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 17 Jan 2009 15:22:11 -0800, Doc wrote: Thanks Chip, You guys are all great! I'm curious though, where did you come up with the "24" for the divisor?? Doc "Chip Pearson" wrote: You don't need to use separators. If your decimal degrees (e.g, 27.5111 = 27 deg, 30 min, 40 sec) is in D5, you can use =INT(D5) to get the degrees =MINUTE(D5/24) to get minutes =SECOND(D5/24) to get seconds If you really want to separate the integral and fractional portions of D5 into separate cells with a single formula, use the following array formula: ={1,0}*INT(D5)+{0,-1}*(INT(D5)-D5) Note the difference between the parens () and the curly braces {}. Select the cells you want the integral and fractional portions to go in, type the formula above and press CTRL SHIFT ENTER instead of ENTER. Since this is an Array Formula, you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces - Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. You can also use two separate formulas to get the integral and fractional portions. =INT(D5) returns the integral portion =D5-INT(D5) returns the fractional portion Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 17 Jan 2009 13:07:01 -0800, Doc wrote: Is there a way to separate and use the numbers, to the left of and to the right of the decimal point in a single cell? I am trying to convert angles given in decimal form (i.e. 27.286), to the older form of degrees, minutes and seconds (i.e. 271710), then store the degrees in one cell, the minutes in another cell, and the seconds in a third. I can make it work the OTHER way with multiplication and addition, because that doesnt require the SEPARATION of a number. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Numbers typed into Excel 2003 read as whole numbers | Excel Discussion (Misc queries) | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
How can I sort mostly 3 decimal with some 4 decimal numbers | Excel Discussion (Misc queries) | |||
How do I sum hours in Excel and convert to decimal numbers? | Excel Discussion (Misc queries) | |||
How do I enter a decimal in a string of numbers in Excel? Example. | Excel Discussion (Misc queries) |