Excel freaks must know how beautifully this tool helps people by introducing multiple features to perform different functions. Here we will discuss TRUNC function and its basic and advanced functioning. How to Truncate numbers in Excel is something that most users need to learn in the beginning. Without further ado, let’s move on to the main topic:
What is the TRUNC Function?
TRUNC basically is an Excel function related to Math and Trigonometry. When you need to cut off the fractional part of a number, this function helps in truncating a number to an integer. You may use it for taking out dates from time and date values. When it comes to financial analysis, the function is helpful to simply truncate a value.
Formula:
=TRUNC(number,[num_digits])
The TRUNC function effectively uses for the following arguments:
- Number – this one is the required argument that truncates the number.
- Num_digits – this one is the optional argument that helps in identifying the precision of the truncation. If you keep it blank, the default value would be 0.
On the other hand, if the num_digits argument is:
- A positive value that is greater than zero, clearly identifies the number of digits to the right of the decimal point. If it is equal to zero, it identifies the rounding to the nearest integer.
- If a negative value is less than zero, it identifies the number of digits to the left of the decimal point.
How is Truncating Used? How to Truncate Numbers in Excel
Let’s learn how you can use the TRUNC function:
We have some situations where you can truncate a number in Excel:
To Create a Whole Number:
When you need to eliminate the fractional part of a number, multiple financial analysts allow you to truncate a number in Excel. You can even turn the number into a whole number that you may call an integer.
If you want to use integers in formulas, it would be easier in comparison with decimal usage usually when the formula has function-limiting characters. With decimals, you may create a whole lot of data for such functions that produce problems. When you plan to truncate a number, you will have a more usable figure but a less precise number.
To Simplify Data into a Standard:
If you need to make a chart or your spreadsheet needs to be sorted down by data, you can easily truncate numbers to make data standards. For instance, you may have a huge list of entries all the way through the year including months and dates, but you only want to sort each one by month. Based on the month, you can truncate values to make categories.
When you want to truncate values to standardize them, you can easily make a chart or graph that needs standardized entries. On the other hand, you can truncate text strings as well as integers that let you edit entries to simplify the data.
To Determine a Date:
In Excel, you may truncate dates and times that let you separate data. Sometimes, you may notice that dates and times are formatted in the same cell just because of some records. This formatting could be standard through the spreadsheet that uses a similar amount of digits for dates and times. Moreover, you can truncate the cell to remove the digits that show the time of day if you want them to be sorted by date.
That’s why if you use the function correctly, it can help you manage data in a better way. For instance, if the cell reads “11/29/21 13:30,” you may truncate the first five characters from the cell so the result would be “11/29/21.”
Things to Consider about the TRUNC Function:
TRUNC and INT both functions are almost similar because both help in returning the integer part of a number. The main difference is that the TRUNC function hardly truncates a number, whereas INT rounds off a number.
Technically, when the TRUNC uses 0 as the default for num_digits and positive numbers, both will return the same results. On the other hand, negative numbers will show different results. =INT(-2.1) returns -3 because INT rounds down to the lower integer. =TRUNC(-2.1) returns -2.
How to Truncate Numbers in Excel?
To learn the basics of using the TRUNC function, we’ve got some examples. Let’s dive in:
Example 1
Suppose we have the following data:
Below you will see the results:
The TRUNC function never rounds off, however, it simply truncates a number as specified. This function is useful to return a set number of decimal places without rounding off with the help of the num_digits argument.
For instance, =TRUNC(PI(),2) will return a Pi value truncated to two decimal digits which would be 3.14 and = TRUNC(PI(),3) will return a Pi value truncated to three decimal places, which would be 3.141.
To Sum Up:
Without practicing, some features could be confusing that’s why you need to exercise more to understand the intent of each function clearly. No matter what, you must know that sometimes only theory is not enough you have to be practically involved in a phenomenon.
