When there is a value in a cell, -3.9876 for instance, how can I remove the whole interger that is not rounded? I mean, the result I am looking for is -0.9876.
When using INT() it seems to work OK with positive numbers, but not negative numbers.
Thanks.
ROUNDDOWN(L3) is yours!
Thanks! Will give it a try. 
From the āwaybackā postsā¦ you were right. ROUNDDOWN().works for me.
Could you explain, please, how this can be understood in the given context where you presented a different āwantedā resuĀ“lt in the image, and claimed INT() would produce the wanted result at least in some cases.
INT returns the next integer n < x
ROUNDOWN returns next integer abs(n) < abs(x)
I am converting decimal degrees to degrees and decimal minutes. For instance, 20.75 degrees to 20 degrees 45 minutes. Some coordinates West or South are negatve. So, using INT() for a positive number, such as 20.75, works. The result is 20. A negative coordinate does not work. Example: the result for -20.75 is -21, not -20.
I am separating the decimal part of the number to convert it to minutes of a degree.
I just wish there was a degrees/minutes/seconds function like there is a hours:minutes:seconds funciton for Time.
Divide the Values by 24,ā¦ =L2/24 ā¦ and use a NumberformatāTime āāFormatcode: [HH] Ā° MM '
Thank you! That was super helpful. Iāll figure out a way to integrate that into my project. Without a lot of explanation, this is what I am working with. They are formulas to calculate where you are in the world and where you should be headed using celestial bodies.
Z formula: DEGREES(ATAN((SinM/((CosLTanD)-(SinLCosM)))))
Hc formula: DEGREES(ASIN((SinLSinD)+(CosLCosD*CosM)))
L is your longitude, D is the lattitude of the object or destination, M is the angle between you and the object or destination.
/Steve
it returns proper results for positive Floats but not for negativesā¦ right?
It may (conditionally) return what the questioner actually wanted but does not comply with
But actuilly thatās all peanuts. The name ROUNDDOW for a function rounding towards zero is a typical spreadsheet-nonsense enforcing bad usage.
What the questioner finally stated was that he(f/m) wanted to convert geographical lengths given in a reasonable way (signed fractional number of degrees) to a textual representation based on the nearly 4000 years old concept of sexagesimal subdivision nearly as many years outdated.
If this is done for historical interest thatās fine. Iām afraid itās done with a different background where the reverse conversion should be preferred.
Anyway: For the given purpose the fractional part is as urgently needed as the integer part.
(The next question may now be how to do the complete formatting to text by a single formula.)
In a playful mood I made a sheet demonstrating how to do that kind of job with a reasonable set of helper columns: textualRepesentationOfAnglesUsingMinutesAndSeconds.ods (25.4 KB)
You may be interested in tdf#141870 .
I agree ā¦ I would expect this for INT( ) , btw. int in python does so!
And why ā¦ is the simple fake Time-format not good enough for you?
Great information and thank you. At sea, beyond the sight of land, youāll find GPS accurate only if you get to where youāre going. Meanwhile, even without GPS, a minute of arc is easier to rationalize and calculate than 0.01666667. Manual instruments for angular measurement are in degrees and minute increments for reasons of convention. A minute of arc relates to 1 nautical mile in the mariners world. Also, arc and time (H:M:S) are relatable to longitude.
Yes the Sumerian sexagesimal is ancient. From it, we have our time measurement convention and a 360-degree globe. The polynesians navigated with a stick. Iām not that goodā¦ and Iām not that good at mathā¦ thatās why I use spreadsheets. 
Itās not about whatās good for me, but whatās consistent - or shoud be suspected to cause new errors.
Basically the trick is clever (and, of course, you know that 3/4 Ā° isntā 45āā but 45ā), but
=TEXT(G10/24;"[hh]Ā°mmā²ssā³")
Iām not complaining. But you accidentaly used the doublequote as the minute-symbol.
We should really stop this discussion. You know yourself that debuggĆng and maintenance for functionality implmented with helper cells is mostly easier and safer than with complicated formulas.
My point was that this is easily slipped based on the view. And ā¦
ā¦ you get a problem wiuth the way back because (e.g.) The TIMEVALUE() function clips off multiples of 24 h.
BTW: I surely am not interested in persuading anybody. All my statements pointed to things I judge to be (sometimes) relevant facts. I may be wrong now and then.
You are the one who wanted to force a text edition at all costs.
my advice was "divide the degrees by 24 and apply a Numberformatcode [HH] ā¦ā¦
no need for missuse of TIMEVALUE etc.
With -3.9876 in A1:
=A1-TRUNC(A1)
=> -0.9876
Expected as defined, INT() rounds down to the nearest integer, towards negative infinity.
TRUNC() rounds towards zero.
Thanks for the TRUNC suggestion. That may work for what I am doing. Meanwhile Iāve posted a working solutionā¦ but this is an ongoing project for me as a pass-time.
Thanks for all of your help.
Iāll fiddle some more with the H:M:S idea. The only problem Iām having is that format does not convert decimal degrees very well. But I have come up with something workable. Hopefully my example of one section showing the meat and potatoes of the issue will upload.
(NOTE: The cell address in the IF formula should be B2.)