What is TRUNC Function in Google Sheets?
The TRUNC function in Google Sheets removes unwanted digits from a given number. In other words, TRUNC shortens a number to a specified number of decimal places. This function is useful because it can simplify large numbers or convert decimal values to integers. You can also use it to filter data in a spreadsheet. The TRUNC function is similar to other rounding functions, such as the INT function in Google Sheets. The difference is that TRUNC accepts an argument and treats the negative numbers differently, which we will discuss further in the topic.
In the example below, the TRUNC function in Google Sheets helps remove a specific number of decimal places from the given number. This function can make it easier to read large numbers. The TRUNC function is designed to truncate numbers by removing decimal places without rounding.
=TRUNC(15.2394567) returns the value 15.
=TRUNC(13.79, 1) returns the value 13.7.
Key Takeaways
- The TRUNC function in Google Sheets removes the decimal part of a number, based on a specified number. However, unlike ROUND, it does not round the number but only removes digits beyond the specified number.
- The syntax of the TURNC function is”
- =TRUNC(value, [places])
- value: The number to truncate.
- places (optional): The number of decimal places to keep. The defualt is zero.
- The TRUNC function works the same whether you use positive and negative numbers. For instance, =TRUNC(1.23, 1) results in 1.2.
- =TRUNC(-1.23, 1) results in -1.2
- The TRUNC function is useful if you always want to round down without the process of rounding.
Syntax
When you truncate a number using TRUNC, you remove the numbers beyond a specified point. It is useful in financial calculations for rounding numbers without any discrepancies.
The syntax for the TRUNC function is:
=TRUNC(value, [places])
- value – The number to be truncated.
- places – An optional argument specifying the number of decimal places. A positive number truncates digits to the right of the decimal point, and a negative number replaces digits to the left of the decimal point. The default value is zero (0).
How to Use TRUNC Function Google Sheets?
The TRUNC function is one of the most widely used rounding functions. It is a versatile tool, offering rounding solutions for different data management scenarios. Now, we can use TRUNC in Google Sheets in two ways.
- TRUNC entered manually
- Selected from the Google Menu Bar
TRUNC entered manually
Below is a list of numbers for which you will apply the TRUNC function manually.
Step 1: Place the cursor in cell B1 to find the nearest integer of the number in cell A1.
Step 2: Enter the formula as shown: =TRUNC(A1,1) and press Enter. Here, the second argument, 1, specifies the number of places to which it is truncated.
Step 3: You get the result 7.6 for the value 7.65. Now, drag the formula to B4.
- As observed in the results, you can see that when rounding off with TRUNC, it does not follow standard rounding rules. It just removes numbers to arrive at the number of places specified, without making any adjustments according to the numbers.
- Even if the number is 0.999(cell A4), it is not rounded to 1.0, but 0.9.
Selected from the Google Menu Bar
Step 1: Choose an empty cell to get the output.
Step 2: Go to the Insert tab followed by Function. Go to the Math option and scroll down to the TRUNC function. Enter the arguments, and press Enter.
Thus, the function can be accessed in two ways, manually or through the Menu Bar, as seen above.
Examples
If you still have doubts about using the TRUNC function in Google Sheets, you can look at some examples below to understand its importance.
Example #1 – TRUNC with Negative Numbers
Now that we have seen how to use TRUNC with decimal numbers let’s look at using TRUNC on negative numbers. Below, we use a few negative numbers and check how Google Sheets truncates them.
Step 1: Below is a number -10.234. Let us keep the places argument at the default of 0. Enter the following function in cell A1.
=TRUNC(-10.234)
Step 2: Press Enter. You can see how the result is handled.
The result is -10. It is crucial to realize had you used a formula like INT, it would give a different result with rounding as seen in the image below.
=INT(-10.234)
Negative Places Parameter
Now that we have seen how to use a negative number with TRUNC let us see how you can use negative values in the places parameter to truncate digits to the left of the decimal point. Enter the following formula in an empty cell.
=TRUNC(1023.234, -2)
It returns 1000 because the number is truncated at the hundreds place. All digits after the hundreds place (23) are removed.
Example #2 – Using TRUNC to Lower a Decimal to a Whole Number
Below is a set of different values. Let us apply the TRUNC function to each number and check the outcome. The TRUNC function removes the decimal part of a number to a specified number of digits without rounding. We also apply the ROUND function and see the difference.
Step 1: Apply the function =TRUNC(A2)) in cell B2 to get the nearest integer for the number in A2.
Press Enter. Now, drag the function to B5.
Step 2: Similarly apply the function =ROUND(A2) in cell C2. Drag the function till C5.
Let us observe each result.
- 987.454321 has been rounded off to 987 by both functions.
- TRUNC has rounded off 654.829987 to 654 while ROUND has rounded it off to 655 as 654.82… has the decimal part greater than or equal to 5.
- TRUNC has rounded off 543.515987 to 543, while ROUND has rounded it off to 544 as the decimal part is equal to 5.
- Similarly, in the case of -2.9, ROUND gives the nearest integer away from zero, -3, while TRUNC removes the decimal and provides an output of -2.
Example #3 – Using TRUNC with FILTER function
You can use this function to FILTER data in a spreadsheet. For example, we have some items and the total amount sold for them. Let us use the following formula to combine TRUNC and FILTER to return all the rows in the table where the value in the Total sold amount column is greater than 10.
Step 1: Enter the following formula in an empty cell.
The formula used: =FILTER(A2:B6, TRUNC(B2:B6) > 10)
Step 2: Press Enter. You can see that the rows with prices greater than $10 after truncation have only been printed.
Note: You can observe that even though the clips were sold for $10.05, which is greater than $10, the TRUNC function rounds it off to $10; hence, the value isn’t printed. So, we should be careful about how we use this function and opt for the best rounding function to suit our needs.
Important Things to Note
- You can also use TRUNC with dates in Google Sheets, as dates are represented as serial numbers. In this case, the TIME portion is truncated.
- TRUNC does not round. For rounding, use the ROUND function instead.
- To customize data handling, you can combine TRUNC with other functions like IF and FILTER in Google Sheets.
Frequently Asked Questions (FAQs)
Besides TRUNC in Google Sheets, there are several functions that deal with rounding. Some of them include:
1. CEILING.MATH – The CEILING.MATH function rounds up a number to the nearest integer or the nearest multiple of specified significance.
2. INT – This function rounds a number down to the nearest integer.
3. ROUND – This function rounds a number to a specified number of decimal places using the regular rounding rules.
4. FLOOR.MATH – It rounds a number down to the nearest integer multiple of specified significance.
5. ROUNDDOWN – It rounds any number down to a specified number of places.
6. ROUNDUP – This function rounds a number up to a specified number of places
For example, if a calculation results in $45.8749, using TRUNC(45.8749, 2) will display $45.87 without any rounding.
The way TRUNC works is by maintaining consistency by removing excess decimals. It is used to preserve the exact calculated values without rounding off based on the decimal. This type of function is helpful when dealing with high-value transactions where the exact number should be preserved and rounding differences could lead to significant discrepancies.
It is also helpful to use during currency conversion as the exchange rates include multiple decimal places, but financial statements require a standardized presentation.
When you use a negative value as the rounding number, the TRUNC function truncates the given number to as many places to the left of the decimal point as specified. For instance:
a)1 truncates the number to the nearest multiple of 10.
b) truncates to the nearest multiple of 100 and so on.
Download Template
This article must help understand TRUNC in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is TRUNC in Google Sheets. We learn its syntax & how to use it to truncate decimal places with examples and working template. You can learn more from the following articles. –
Leave a Reply