How to get the decimal part of numbers in a data set

by | Apr 5, 2018 | Excel Tips & Tricks

How to get the decimal part of numbers in a data set

Identifying the detail level of values in a data set can be useful for analysis of results. This simple trick can help you separate the decimal values from the numbers for evaluation.

Syntax

=number - TRUNC(number)

(only for positive numbers) =number - INT(number)

Steps

  1. Select or type range reference that includes the value B3
  2. Type - (minus sign)
  3. Type TRUNC(
  4. Select or type same range reference that includes the value B3
  5. Type ) and press Enter to complete formula

Note: To get integer value by TRUNC function, it is enough to omit 2nd argument rather than typing 0 which means no decimal digits as well.

How

TRUNC function returns the exact integer part of a number without rounding it. INT function returns exactly same result with TRUNC for positive numbers. INT function rounds negative number to lower number. Because of this reason using TRUNC is safer. All need to do is to subtract integer value found by TRUNC or INT, from original value.

=B3 - TRUNC(B3)

= B3 - INT(B3)