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

edit retag close merge delete

Sort by » oldest newest most voted

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)))

more

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?

more