11

I've got a number in a spreadsheet like so: 28.686279

When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, is there a formula that returns the "decimal part", I.e. 0.686279?

5 Answers 5

17

TRUNC() is designed to remove the decimal part of any number immediately and without any modification to the non-decimal part.

So, as per LinYan's answer, you just need to use:

  • TRUNC(A1) to obtain the integer part of the value in A1
  • A1-TRUNC(A1) to obtain the fractional part of the value in A1

Unlike FLOOR(), TRUNC() works on both positive and negative numbers without requiring adjustment, and works in the same way in both Microsoft Excel and LibreOffice.

FLOOR() requires the significance parameter to have the same sign as the number being processed (or else will throw an error), so the 1 at the end would have to be changed to -1 to process negative numbers, or you could insert SIGN() and unnecessarily complicate the formula further.

And, in OpenOffice and LibreOffice, FLOOR() also has an additional (compared to Excel) third "mode" parameter that changes the results that the function returns for negative numbers.

8

you can try FLOOR function, floor(A1,1) for integer part of A1, A1-floor(A1,1) for decimal part of A1.

2
  • 1
    Just wondering, why isn't there some form of FRAC(A1) function to retrieve the decimal part? It seems that's something people do quite often, right? Aug 29, 2013 at 22:50
  • How about negatives... Nov 17, 2016 at 11:06
5

For example, imagine A1 is 167.583 :
int(A1) would give 167 and
mod(A1,1) would give 0.583 .

1
  • You do realise that int(1.6) === 1 and int(-1.6) === -2... But trunc(decimal;0) works correctly for both. Nov 17, 2016 at 11:05
1

Don't think there's a specific function to do this, however by nesting a couple you can.

Assuming you're trying to return the right-of-decimal value for cell A1, the formula would be:

=MID(A1,SEARCH(".",A1,1)+1,LEN(A1))

Effectively what you're doing here is using the MID function to return some number of characters starting at the decimal point. All 3 references to A1 need to be updated for each cell you're targeting for it to work correctly.

1
  • This returns text rather than a number.
    – fixer1234
    Dec 3, 2014 at 9:09
-3
=RIGHT(TEXT(ABS(A1)-INT(ABS(A1));",00");2)

would'nt this be just perfect, and aesthetic, too?

1
  • This returns text rather than a number.
    – fixer1234
    Dec 3, 2014 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.