Forum Discussion

weikiat98's avatar
weikiat98
Copper Contributor
Jun 10, 2023

formula to convert degree minutes to degree decimals

Hello all, 

 

is there a better formula to convert degree minutes to degree decimals in excel? The formulas I currently use produce some inconsistencies coordinate points on my map. Refer to picture and formulas below for reference.

 

The current formula I use for Latitude in context of cell N2: =IF(OR(RIGHT(L2,1)="S", RIGHT(L2,1)="s"), -1, IF(OR(RIGHT(L2,1)="N", RIGHT(L2,1)="n"), 1, "")) * (VALUE(LEFT(L2, FIND("-", L2)-1)) + (VALUE(MID(L2, FIND("-", L2)+1, LEN(L2)-FIND("-", L2)-1)) / 60))

 

Longitude formula in column O using cell M2: =IF(RIGHT(M2,1)="W", -1, 1) * (VALUE(LEFT(M2, FIND("-", M2)-1)) + (VALUE(MID(M2, FIND("-", M2)+1, LEN(M2)-FIND("-", M2)-1)) / 60)) 

 

Thanks

 

 

 

 

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 10, 2023

    weikiat98 

    I'm not sure what are formats for latitude and longitude, but you may simplify your formula at least to

    =IF( RIGHT(L2, 1) = "S", -1,
 IF(RIGHT(L2, 1) = "N", 1, "")) *
(
    LEFT(L2, FIND("-", L2) - 1) +
    MID( L2, FIND("-", L2) + 1, LEN(L2) - FIND("-", L2) - 1 ) / 60
)

    taking into account comparison in Excel is case insensitive by default and in arithmetic operations numbers as texts are automatically converted to numbers, e.g. = "1"+2 returns 3

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 10, 2023

    weikiat98 

    The formulas you are currently using to convert degree minutes to degree decimals in Excel seem correct. However, if you are experiencing inconsistencies with the coordinate points on your map, there could be other factors at play, such as data formatting or rounding errors.

    Here are the formulas you provided for reference:

    Latitude formula in cell N2:

    =IF(OR(RIGHT(L2,1)="S",RIGHT(L2,1)="s"),-1,IF(OR(RIGHT(L2,1)="N",RIGHT(L2,1)="n"),1, ""))*(VALUE(LEFT(L2,FIND("-",L2)-1))+(VALUE(MID(L2,FIND("-",L2)+1,LEN(L2)-FIND("-",L2)-1))/60))

    Longitude formula in cell O2:

    =IF(RIGHT(M2,1)="W",-1,1)*(VALUE(LEFT(M2,FIND("-",M2)-1))+(VALUE(MID(M2,FIND("-",M2)+1, LEN(M2)-FIND("-",M2)-1))/60))

    If you want to check for any potential issues, you can consider the following:

    1. Ensure that the data in columns L and M (containing the degree minutes) is formatted correctly as text or general.
    2. Check for any leading or trailing spaces in the degree minute values that might affect the calculations.
    3. Make sure the degree minute values are entered consistently and follow the correct format (e.g., "35-30" for 35 degrees 30 minutes).
    4. Verify that the resulting decimal values are rounded to the appropriate number of decimal places for your map's accuracy requirements.

     

Resources