TRUNCformula returns a number truncated to a specific number of decimal places. It is commonly used to remove decimal places from a number without rounding. This formula is often used in financial calculations and data analysis where exact values are important.
- How to use
- Examples of using
TRUNCformula not working?
- Similar formulas to
TRUNC formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- value (required):
The number that you want to truncate.
- places (optional):
The number of decimal places to which you want to truncate the value. If omitted, the value will be truncated to zero decimal places.
ExamplesHere are a few example use cases that explain how to use the
TRUNCformula in Google Sheets.
Truncate a currency amount
When working with financial data, it is important to maintain the exact value of currency amounts. The
TRUNC formula can be used to remove any decimal places from a currency amount, ensuring that the value remains accurate.
Truncate a percentage
Percentages are often used to represent a proportion or rate. However, when working with percentages, it is not always necessary to display them with a high degree of precision. The
TRUNC formula can be used to remove excess decimal places from a percentage, making it easier to read and understand.
Truncate a calculated value
When performing calculations in a spreadsheet, it is often necessary to round or truncate the result to a specific number of decimal places. The
TRUNC formula can be used to truncate the result of a calculation to the desired number of decimal places.
TRUNCnot working? Here are some common mistakes people make when using the
TRUNCGoogle Sheets Formula:
Not providing a value argument
One common mistake is forgetting to provide a value argument to the TRUNC function. Without a value, the function cannot perform any truncation.
Providing a non-numeric value argument
TRUNC can only operate on numeric values. If a non-numeric value is passed as an argument, the function will return an error. Make sure the argument is a number or can be coerced into a number.
Providing a negative value for places
Places argument in TRUNC should be a positive integer or zero. If a negative value is provided, the function will return an error. Ensure that the places argument is a non-negative integer.
Rounding instead of truncating
TRUNC only truncates the decimal part of a number. If you want to round a number to a certain number of decimal places, use the ROUND function instead.
Incorrectly using the INT function instead of TRUNC
Although the INT function also truncates a number, it behaves differently than TRUNC when it comes to negative numbers. INT rounds negative numbers towards zero, while TRUNC simply removes the decimal part. Make sure you are using the correct function for the desired result.
The following functions are similar to
TRUNC or are often used with it in a formula:
ROUNDformula rounds a number to a specified number of decimal places. It is commonly used to simplify large numbers or to make a number more readable. The formula can round both positive and negative numbers. If the
placesparameter is not specified, the formula rounds to the nearest integer.
CEILINGfunction returns a number rounded up to the nearest multiple of a specified factor. It is commonly used to round up prices to the nearest dollar or to adjust numbers to fit into specific increments.
The FLOOR formula rounds a given number down to the nearest multiple of a specified factor. It is commonly used when dealing with financial data or when working with time values. The function takes a value and an optional factor as arguments and returns the rounded down value.
You can learn more about the
TRUNC Google Sheets function on Google Support.