INT Function in Microsoft Excel
Part 1: Introduce
Definition:
The INT function in Microsoft Excel rounds a number down to the nearest integer.
Purpose:
To truncate an actual number and return its integer portion, effectively rounding down.
Syntax & Arguments:
syntax
INT(number)
Explain the Arguments in the function:
- Number: Required. This is the actual number you want to round down to an integer.
Return value:
The INT function will return the integer portion of the provided number by rounding it down.
Remarks:
- Positive numbers are truncated to remove any decimal portion.
- Negative numbers are rounded away from zero. For instance, -8.9 would become -9.
Part 2: Examples
Example 1:
- Purpose of illustration: To round down the number 8.9.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Data | Formula | Result |
| 2 | 8.9 | =INT(A2) | 8 |
- Explanation: The number 8.9 is rounded down to 8.
Example 2:
- Purpose of illustration: To round down the number -8.9.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Data | Formula | Result |
| 2 | -8.9 | =INT(A2) | -9 |
- Explanation: The number -8.9 is rounded down away from zero to -9.
Example 3:
- Purpose of illustration: To retrieve the decimal part of a number.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Data | Formula | Result |
| 2 | 19.5 | =A2-INT(A2) | 0.5 |
- Explanation: By subtracting the integer portion from the original number, we get the decimal part, 0.5, for 19.5.
Example 4:
- Purpose of illustration: To round down the average of three numbers.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 10.5 | 20.7 | 30.3 | =INT(AVERAGE(A2:C2)) | 20 |
- Explanation: The average of 10.5, 20.7, and 30.3 is 20.5. Using the INT function, this is rounded down to 20.
Example 5:
- Purpose of illustration: To round down the sum of two numbers.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 15.2 | 25.8 | =INT(A2+B2) | 41 |
- Explanation: The sum of 15.2 and 25.8 is 41. Using the INT function, this remains 41 as it’s already an integer.
Example 6: Using INT with IF Function
- Purpose of example: To round down a number if it’s greater than 10; otherwise, return “Too Small”.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Data | Formula | Result |
| 2 | 11.9 | =IF(A2>10, INT(A2), "Too Small") | 11 |
- Explanation: Since 11.9 is greater than 10, it’s rounded to 11.
Example 7: Using INT with SUM Function
- Purpose of example: To round down the sum of three numbers.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 12.3 | 15.7 | 18.9 | =INT(SUM(A2:C2)) | 46 |
- Explanation: The sum of 12.3, 15.7, and 18.9 is 46.9. Using the INT function, this is rounded down to 46.
Example 8: Using INT with VLOOKUP Function
- Purpose of illustration: To round down a value retrieved using VLOOKUP.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Key | Value | Formula | Result |
| 2 | 1 | 24.7 | =INT(VLOOKUP(1, A2:B2, 2, FALSE)) | 24 |
- Explanation: The VLOOKUP function retrieves the value 24.7 for the key 1. This value is then rounded down to 24 using the INT function.
Example 9: Using INT with AVERAGE Function
- Purpose of example: To round down the average of three numbers.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 10.8 | 20.6 | 30.4 | =INT(AVERAGE(A2:C2)) | 20 |
- Explanation: The average of 10.8, 20.6, and 30.4 is 20.6. Using the INT function, this is rounded down to 20.
Example 10: Using INT with MAX Function
- Purpose of illustration: To round down the maximum value among three numbers.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 14.2 | 28.5 | 42.9 | =INT(MAX(A2:C2)) | 42 |
- Explanation: The maximum value among 14.2, 28.5, and 42.9 is 42.9. Using the INT function, this is rounded down to 42.
Example 11: Using INT with MIN Function
- Purpose of illustration: To round down the minimum value among three numbers.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 22.8 | 33.3 | 44.1 | =INT(MIN(A2:C2)) | 22 |
- Explanation: The minimum value among 22.8, 33.3, and 44.1 is 22.8. Using the INT function, this is rounded down to 22.
Example 12: Using INT with CONCATENATE Function
- Purpose of example: To concatenate a string with the rounded-down value of a number.
- Data sheet and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Data | Formula | Result |
| 2 | 40.6 | =CONCATENATE("Rounded Value: ", INT(A2)) | Rounded Value: 40 |
- Explanation: The number 40.6 is rounded down to 40. The CONCATENATE function then combines this value with the “Rounded Value: “string, resulting in the final text “Rounded Value: 40”.
Part 3: Tips and tricks
- The INT function is beneficial when you must ensure that a number is an integer, such as when working with counts, whole numbers, or indices.
- Remember that the INT function always rounds down, even if the decimal portion is more significant than 0.5.
- For positive numbers, the INT function behaves similarly to the TRUNC function.
- Be cautious when working with negative numbers, as the INT function rounds away from zero.