Rationalizing sexadecimal to decimal

We have 23deg in column A and 36min in column B

Converting 23deg 36 min to decimal is straight forward, we put in C =A+B/60, result 23.60

But if it is minus as in -23deg 36min the result is -22.40

Even making ABS(23)+36/60 results 23.60

How do we get the correct value -23.60 in column C ?


Sorry. I obviously don’t understand.
Why would you expect =-23 + 36/60 to result in -23.6? General conventions teached everywhere(?) would tell you to need =-(23+36/60) for what you want to get.
If you actually want to have the sign of the first addend of two to rule all the sum against the convention, you would need to use =SIGN(firstAddend) * (ABS(first Addend) + secondAddend), but I am afraid you would be discontent with the result in cases of a negaive secondAddend. This isn’t anything to do with the specific divisor of 60 (conversion from sexagesimal representation to decimal).

In addition: How did you enter 23deg, how 36 min? Why with a space in on case and without in the other? Are you reporting due to a number format applied to the respective cells? After all 36' taken seriously already is 36/60° by its generally accepted meaning.
Why don’t you enter 23°36' or -23°36' as a text into a single cell, and then convert it in one step?

Put a negative value in B or store the sign in a third column?

But if you insist on storing the minutes as a positive value when the degrees are negative, try =IF(A2>=0;A2+(B2/60);A2-(B2/60))

If this answer helped you, please accept it by clicking the check mark ? to the left and, karma permitting, upvote it. That will help other people with the same question.

In case you need clarification, edit your question (don’t use an answer) or comment the relevant answer.