Google Sheets Formula for Fractional Part of a Number

google sheets

I need a formula to get only the fractional part of a given number. It should take any number and return its fractional part which is between 0 and 0.9999…
For example

123.456 -> 0.456
      0 -> 0
      1 -> 0
    0.1 -> 0.1

I tried google help, but did not find such a function.
I could use something like this, but I did not want to repeat the input expression: X - FLOOR(X)

Best Answer

The following worked for me, however it was not documented in the google help

=MOD(X;1)

Related Solutions

Google-sheets – Change an input value in a cell to another value depending on what the input was

This is more a workaround than a real solution, but add this to the scripteditor and change to sheet name..

function onEdit(e) {
if (e.source.getActiveSheet()
    .getName() !== 'Sheet2' || e.range.columnStart !== 7) return;
e.range.setFormula("=rept(char(9734)," + e.value + ")")
}

Since I do not know how to work with a star symbol (char) in GAS, the script writes a formula to the edited cell (in col G) which replaces the number that is typed in with that number of stars.

Google Sheets – Create Sparkline for Delta Between Columns

Use arrayformula to perform operations on arrays, for example

=sparkline(arrayformula(B2:E2 - A2:D2))

plots the sparkline of differences B2-A2,..., E2-D2.

Best Answer

Related Solutions

Google-sheets – Change an input value in a cell to another value depending on what the input was

Google Sheets – Create Sparkline for Delta Between Columns

Related Topic