NavList:

Hello Phil.

You wrote:
"I distilled down all the steps given the in a very helpful guide I had found and then struck a problem. How to format excel cells for degrees/minutes/decimal minutes (DMD)?"

Math is easy. Formatting output as text is hard. This is a Law of Nature, and it has been so since Fortranosaurus rex ruled the Earth. You should ask yourself: why do you need it? If I calculate my latitude and get 41.225°, isn't that good enough? If you do occasionally need the result in degrees and minutes, you can do that quickly on a calculator and handle all the tricky cases in your head.

But... you learn a lot about formatting in spreadsheets by dealing with this issue. And that pays off later, too.

Some issues:

• What happens when your angle ends in a string of nines? If the angle is 12.9999 decimal degrees, then that should display as 13°00.0' and not 12°60.0'.
• What happens when your angle is negative?
• If your angle is very close to zero, does it display correctly? And if negative and very close to zero?
• Use the TEXT function to convert numbers to text strings. It takes a numerical input and a formatting string as arguments. Formatting strings can be intimidating. The simplest case is TEXT(A1, "#"). A more interesting case is TEXT(A1, "0.0"). Understand how those two cases work, and you'll have most of the good stuff. Learn more format strings later.
• Handle the "signs" separately using a simple conditional statement. In modern spreadsheets, conditionals are easy. For example, the statement IF(A1<0,"S","N") is read as if condition then... else... and in thise case would yield "S" for a negative number (only) in cell A1 and "N" for all other cases (including exactly zero, which should not be a problem).
• Avoid magic single-cell code. Spread your work out over multiple cells. If they clutter the spreadsheet, then place those "working" cells off-screen. For example you could have ten cells in a row starting at "AA1" running to "AJ1" working each step of converting the decimal angle to a text string. Then back in the visible portion of the spreadsheet, you would have a cell formula maybe in cell "D1" that has the simple formula "=AJ1".

Please see the attached simple spreadsheet illustrating some examples and cases.

Sean mentioned a keyboard code trick to enter the degree symbol (alt plus some numbers). That's great if you're using "Windows" and your device (laptop?) has a full keyboard with a numeric keypad. So it's specific to certain hardware/software systems. The best alternative that I know --assuming you're not going to be typing degree symbols regularly (as I do, and for which there are better solutions)-- is to keep a small text file easily accessible containing all the interesting characters that you think you might use. I keep this right at the top of my 'non-critical passwords' file. Here's a short list:

° × √ ∂ ∇ ∑ ≠ ≅ ≈ — ƒ · • ¦ | ⁻⁰¹²³⁴⁵⁶⁷⁸⁹ⁿ ₀₁₂₃₄₅₆₇₈₉₊₋

Just copy that into a text file and then you can grab a character when needed. Or come back to this NavList message to copy from, as needed. If those options are too much trouble, you can always just ask your favorite search engine. Try a Google search for degree symbol: https://www.google.com/search?q=degree+symbol.  Here's one simple website that will turn up in searches and that you may find useful: https://degreesymbol.co/. This one conveniently includes a "copy to clipboard" button.

Frank Reed