The TRUNC and MOD functions in Excel are essential tools for working with numbers. This tutorial will guide you through their usage with clear examples and practical applications.
TRUNC Function
The TRUNC function truncates a number to a specified number of decimal places by removing digits, not rounding.
Syntax
TRUNC(number, [num_digits])
- number: The number you want to truncate.
- num_digits: (Optional) The number of decimal places to keep. Default is 0.
TRUNC Function Examples
Example 1: Basic Truncation
To truncate 8.9 to an integer:
=TRUNC(8.9)
This will return 8 (not 9, as it doesn’t round up).
Example 2: Truncating to Decimal Places
To truncate 3.14159 to 2 decimal places:
=TRUNC(3.14159, 2)
This will return 3.14 (removes digits without rounding).
Example 3: Negative Numbers
To truncate -5.7:
=TRUNC(-5.7)
This will return -5 (removes the decimal portion).
Example 4: Truncating to Tens or Hundreds
To truncate 1234 to the nearest hundred:
=TRUNC(1234, -2)
This will return 1200 (negative num_digits truncates to the left of decimal).
Interactive TRUNC Demo
MOD Function
The MOD function returns the remainder after dividing one number by another (the modulo operation).
Syntax
MOD(number, divisor)
- number: The number for which you want to find the remainder.
- divisor: The number by which you want to divide.
MOD Function Examples
Example 1: Basic Modulo
To find the remainder of 10 divided by 3:
=MOD(10, 3)
This will return 1 (10 ÷ 3 = 3 remainder 1).
Example 2: Testing Even/Odd Numbers
To check if a number is even or odd:
=MOD(7, 2)
This will return 1 (odd). If the result is 0, the number is even.
Example 3: Cycling Through Values
To create a repeating pattern (e.g., for row shading):
=MOD(ROW(), 2)
This will return 0 for even rows and 1 for odd rows.
Example 4: Decimal Numbers
To find the remainder with decimals:
=MOD(5.5, 2)
This will return 1.5 (5.5 ÷ 2 = 2 remainder 1.5).
Interactive MOD Demo
Excel TRUNC and MOD functions – Practical Applications
TRUNC Applications:
- Financial calculations requiring exact truncation (not rounding)
- Extracting the integer portion of measurements
- Data cleaning and formatting
- Removing unwanted decimal precision
MOD Applications:
- Alternating row colors in spreadsheets
- Creating repeating patterns or cycles
- Checking divisibility of numbers
- Time calculations (e.g., converting minutes to hours and minutes)
- Scheduling and rotation systems
Excel TRUNC and MOD functions -Tips and Tricks
- TRUNC vs ROUND: TRUNC removes digits while ROUND adjusts them based on value.
- TRUNC vs INT: Both remove decimals, but INT rounds down while TRUNC just removes.
- MOD with negative numbers: The sign of the result matches the divisor’s sign.
- Combine MOD with IF for conditional formatting based on divisibility.
- Use TRUNC with dates to remove time components from datetime values.
- MOD is perfect for creating alternating patterns using formulas like =MOD(ROW(), 3) for every 3rd row.