Ask Your Question
0

Add decimal part

asked 2019-02-20 08:46:10 +0100

kik94 gravatar image

updated 2019-02-20 09:36:49 +0100

hello i have a range of column in a spreadsheet (from A1 to A30) filled with decimal numbers. Now i want to get the sum only of the decimal part. For example :

if i have this numbers :

  • 1.3
  • 1.5
  • 0.8
  • -1.3
  • -0.2
  • 0.4

then the sum of the decimals should be : 1.5

Any idea how i could do this ?

Also do the same with the integer part of them

I have tried =SUM(MOD(A1;1):MOD(A6;1)) but no result

Thanks in advance

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2019-02-20 10:39:01 +0100

updated 2019-02-20 10:51:07 +0100

Initial formulas:

=SUMPRODUCT(SIGN(A1:A6);MOD(ABS(A1:A6);1))
=SUMPRODUCT(ROUNDDOWN(A1:A6))

Simplification of the first formula - borrowing clever usage of MOD by @libreofficeUser30872:

=SUMPRODUCT(MOD(A1:A6;SIGN(A1:A6)))
edit flag offensive delete link more
0

answered 2019-02-20 16:04:52 +0100

Lupp gravatar image

Just for a specific type of purists:
=SUMPRODUCT(SIGN(A1:A6);ABS(A1:A6)-INT(ABS(A1:A6)))
"Purist" in the sense of someone doubting e.g. whether MOD for non-positive second argument (divisor) has a reliable "interapplicational" specification.
BTW: Is there a use case to depict with a few words?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-02-20 08:46:10 +0100

Seen: 50 times

Last updated: Feb 20