INT Function in Excel (Table of Contents)
INT in Excel
Int function is a very simple function, which is used to convert any number into an integer value. Integer values are any number which is a whole number but it can be a positive number or negative number. Int function can consider any number whether it is decimal, fraction, or square root value, but in the end, we will be getting a whole number out it.
INT Formula in Excel:
Below is the INT Formula in Excel.
where
Number or Expression – It is the number you want to round down to the nearest integer.
How to Use INT Function in Excel?
INT function in Excel is very simple and easy to use. Let understand the working of INT function in Excel by some INT Formula example. INT function can be used as a worksheet function and as a VBA function.
Example #1
In the below-mentioned table, it contains a value in cell “C8” i.e. 6.79 which is a positive number, I need to find out the nearest integer for 6.79 using INT function in Excel.
Select the cell “E8” where the INT function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, Type the keyword “INT” in the search for a function box, INT function will appear in select a function box. Double click on INT function.
A dialog box appears where arguments (number) for the INT function need to be filled or entered.
i.e. =INT(C8).
It removes decimal from the number and returns the integer part of the number i.e. 6.
Example #2
In the below-mentioned table, it contains a value in cell “C14” i.e. -5.89 which is a negative number, I need to find out the nearest integer for -5.89 using the INT function in Excel. Select the cell E14 where the INT function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, Type the keyword “INT” in the search for a function box, INT function will appear in select a function box. Double click on INT function.
A dialog box appears where arguments (number) for the INT function need to be filled or entered.
i.e. =INT(C14)
It removes decimal from the number and returns the integer part of the number i.e. -6.
When the INT function is applied for a negative number, it rounds down the number. i.e. It returns the number which is lower than the given number (More negative value).
Example #3
In the below mention example, I have the date of birth ( 16th May 1982) in the cell “J8”, I need to calculate the age in cell “L8” using the INT function in Excel.
Prior to the INT function in Excel, let’s know about the YEARFRAC function, YEARFRAC function returns a decimal value that represents fractional years between two dates. I.e. Syntax is =YEARFRAC (start_date, end_date, [basis]) it returns the number of days between 2 dates as a year.
Here the INT function is integrated with YEARFRAC function in cell “L8”.
YEARFRAC formula takes the date of birth and the current date (which is given by the TODAY function) and it returns the output value as age in years.
i.e. =INT(YEARFRAC(J8,TODAY()))
It returns the output value i.e. 36 years.
Example #4
Usually Excel stores date value as a number, where it considers date as an integer, and the time as a decimal portion. If a cell contains a date and time as a combined value, you can extract date value only, by using the INT function in Excel. Here cell “P8” contains date and time as a combined value. Here I need to specifically extract date value in cell “R8”
Select the cell R8 where the INT function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “INT” in the search for a function box, INT function will appear in select a function box. Double click on INT function.
A dialog box appears where arguments (number) for the INT function need to be filled or entered.
i.e. =INT(P8)
It removes a decimal portion from the date & time value and returns only the date portion as a number, where we need to discard the fraction value by formatting in output value.
i.e. 11/12/18
Example #5
In the below-mentioned table, it contains a value less than 1 in cell “H13” i.e. 0.70 which is a positive number, I need to find out the nearest integer for decimal value, i.e. 0.70 using INT function in Excel. Select the cell I13 where the INT function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, Type the keyword “INT” in the search for a function box, INT function will appear in select a function box. Double click on INT function.
A dialog box appears where arguments (number) for the INT function need to be filled or entered.
i.e. =INT(H13)
Here, it removes decimal from the number and returns the integer part of the number i.e. 0
Things to Remember
- In INT function, Positive numbers are rounded toward 0 while negative numbers are rounded away from 0. E.G. =INT(2.5) returns 2 and =INT(-2.5) returns -3.
- Both INT() and TRUNC() functions are similar when applied to positive numbers, both of them can be used to convert a value to its integer portion.
- If any wrong type of argument is entered in the syntax of the function, it results in #VALUE! error.
- In INT function, if the referred cell is not valid or invalid reference, it will return or results in #REF! error.
- #NAME? error occurs when the excel does not recognize specific text in the formula of INT function.
Recommended Articles
This has been a guide to INT Function in Excel. Here we discuss the INT Formula in Excel and how to use INT function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –