INT Function in Excel (Table of Contents)
INT in Excel
- INT function in Excel is used as worksheet or VBA function in excel. It’s an in-built function in excel that is categorized under math/trig function.
- INT function in Excel is used as a part of a formula in a cell of a worksheet & also integrated with other excel functions.
- INT function is an excel function to round numbers, most commonly used to round off currency values & to round a decimal value to an integer.
- Out of all excel round functions, INT function in Excel is simple and easy to use, because it requires only one argument.
Definition
INT – round the number down to the nearest integer.
i.e. It returns only the integer part of a number by removing decimals from it.
Actually, INT function truncates a supplied number down to the closest integer.
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 VBA function.
Example #1 – INT Function on Positive value or Number
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 nearest integer for 6.79 using INT function in Excel.
Select the cell “E8” where INT function needs to be applied.
Click the insert function button (fx) under 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 INT function needs 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 – INT Function on Negative value or Number
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 nearest integer for -5.89 using INT function in Excel.
Select the cell E14 where INT function needs to be applied.
Click the insert function button (fx) under 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 INT function needs 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 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 – INT Function to Find out Age Using Date of Birth
In the below mention an example, I have the date of birth ( 16th May 1982) in the cell “J8”, I need to calculate the age in cell “L8” using INT function in Excel.
Prior to INT function in Excel, let’s know about 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 – INT Function to Extract only Date value From a Date & Time Table
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 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 INT function needs to be applied.
Click the insert function button (fx) under 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 INT function needs to be filled or entered.
i.e. =INT(P8)
It removes a decimal portion from 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 – INT Function For the Value Less Than 1
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 nearest integer for decimal value, i.e. 0.70 using INT function in Excel.
Select the cell I13 where INT function needs to be applied.
Click the insert function button (fx) under 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 INT function needs 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 about the INT Function in Excel:
- 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 –
