Do you want to effortlessly manipulate numbers in Google Sheets? Look no further than the TRUNC function! In this article, we’ll dive deep into the TRUNC function and explore its powerful features. We’ll also compare it with the INT and ROUND functions to help you understand the differences. So, let’s get started!
The Basics of TRUNC Function
The TRUNC function in Google Sheets is a versatile tool that allows you to reduce the number of decimal places in a given value by discarding insignificant digits. Unlike more complex functions, TRUNC is user-friendly and doesn’t require extensive knowledge to use effectively.
To use the TRUNC function, you need to know its syntax and arguments:
Syntax:
TRUNC(value, [places])
Arguments:
- value: The value to be truncated, either as a cell reference or hardcoded.
- places: (Optional) A positive or negative number that determines the number of decimal places to retain or discard.
Now that we have the basics covered, let’s explore some examples to see the TRUNC function in action!
Examples of TRUNC Function
Let’s say we have a value of 1510.295. By applying different values for ‘places’, we can better understand the behavior of the TRUNC function.
-
If ‘places’ is set to -4, the formula will return 0, as it changes the four digits to the left of the decimal point to 0 and discards the rest:
TRUNC(1510.295, -4) // returns 0 -
Setting ‘places’ to -3 will result in 1000, keeping the three digits to the left of the decimal point:
TRUNC(1510.295, -3) // returns 1000 -
To truncate all decimal places except two, set ‘places’ to 2:
TRUNC(1510.295, 2) // returns 1510.29 -
If ‘places’ is 0 or unspecified, the formula will truncate all decimal places:
TRUNC(1510.295) // returns 1510
Check out the following screenshot for a clear representation of the formula in action: Example Screenshot
The TRUNC function handles both negative and positive values effortlessly. The result will be positive or negative, depending on the sign of the values in the given range.
Removing Decimal Places in Google Sheets Made Easy
From the examples above, it’s evident that the TRUNC function can come in handy when you need to eliminate decimal places in Google Sheets. Simply wrap the value with TRUNC as follows:
=TRUNC(value)
For instance, to remove decimal places from the value 1500.12, use the following formula:
=TRUNC(1500.12) // returns 1500
You can also use TRUNC on a cell reference, like this:
=TRUNC(A1)
Additionally, you can leverage the TRUNC function to extract the date from a timestamp by truncating the time component in a datetime value.
Understanding the Difference: ROUND vs. TRUNC Functions
While both the ROUND and TRUNC functions share similarities in their arguments, they have distinct behaviors.
The ROUND function rounds a value to a specific number of decimal places, similar to TRUNC. However, the key difference lies in how they treat the ‘places’ argument.
Let’s consider a value of 1510.295 and ‘places’ set to -3. Here’s how the TRUNC and ROUND formulas handle these numbers:
=TRUNC(A3, B3) // returns 1000
=ROUND(A3, B3) // returns 2000
When ‘places’ is set to 2, the formulas will return 1510.29 and 1510.3, respectively. For a visual comparison, refer to the following screenshot: Comparison Screenshot
Exploring the INT Function and Its Relation to TRUNC
The INT function in Google Sheets extracts the integer part of a number. Similar to TRUNC, it only requires the ‘value’ argument.
When using TRUNC with only the ‘value’ argument, it also returns the integer part of the number. However, there is a distinct difference.
The INT function always rounds numbers down to the nearest integer that is less than or equal to it. This becomes apparent when dealing with negative numbers.
For example, if you use -1500.59 within INT and TRUNC, they will return -1501 and -1500, respectively.
In Conclusion
Although we have touched on the differences between INT and ROUND with TRUNC, our main focus throughout this tutorial has been the powerful TRUNC function.
By understanding the TRUNC function’s syntax, its practical applications, and its distinctions from INT and ROUND, you are now equipped to harness the full potential of Google Sheets.
For more valuable resources and tutorials on Google Sheets and beyond, visit Crawlan.com.
Happy truncating!
