The INT function in Excel returns the integer part of a number, discarding any fractional part. For example, =INT(3.14) returns 3, and =INT(-4.5) returns -5.
The IF function in Excel evaluates a logical condition and returns one value if the condition is true, and another value if the condition is false. For example, =IF(A1>10,"High","Low") returns “High” if the value in cell A1 is greater than 10, and “Low” otherwise.
You can combine the INT function with the IF function to perform calculations based on the integer part of a number. For example, you can use the formula =IF(INT(B2)=B2,"Even","Odd") to check if a number in cell B2 is even or odd, and return the corresponding text.
Procedure
To use the INT function with the IF function in Excel, follow these steps:
- Enter your data in a worksheet. For example, you can enter some numbers in column B, as shown below:
| A | B |
|---|---|
| 3.14 | |
| 6.28 | |
| 9.42 | |
| 12.56 | |
| 15.7 |
- In another column, enter the formula
=IF(INT(B2)=B2,"Even","Odd")in the first cell, and copy it down to the rest of the cells. For example, you can enter the formula in column C, as shown below:
| A | B | C |
|---|---|---|
| 3.14 | Odd | |
| 6.28 | Even | |
| 9.42 | Odd | |
| 12.56 | Even | |
| 15.7 | Odd |
- The formula will return “Even” if the number in column B is an integer and divisible by 2, and “Odd” otherwise.
Explanation
The formula =IF(INT(B2)=B2,"Even","Odd") works as follows:
- The INT function returns the integer part of the number in cell B2. For example, if B2 contains 3.14, the INT function returns 3.
- The IF function compares the result of the INT function with the original number in cell B2. If they are equal, it means that the number is an integer and has no fractional part. For example, if B2 contains 6.28, the INT function returns 6, and the IF function compares 6 with 6.28, which are not equal.
- The IF function then returns the value specified for the true or false argument, depending on the result of the comparison. For example, if B2 contains 9.42, the INT function returns 9, and the IF function compares 9 with 9.42, which are not equal. The IF function then returns “Odd”, which is the value specified for the false argument.
Scenario
Suppose you have a list of sales amounts in column B, and you want to calculate the commission for each sale based on the following rules:
- If the sale amount is less than $100, the commission is 5% of the sale amount.
- If the sale amount is between $100 and $500, the commission is 10% of the sale amount.
- If the sale amount is more than $500, the commission is 15% of the sale amount.
You can use the INT function with the IF function to calculate the commission for each sale, as shown below:
| A | B | C |
|---|---|---|
| Sale | Amount | Commission |
| 1 | $75 | =IF(INT(B2/100)=0,B2*0.05,IF(INT(B2/100)=B2/100,B2*0.1,B2*0.15)) |
| 2 | $150 | =IF(INT(B3/100)=0,B3*0.05,IF(INT(B3/100)=B3/100,B3*0.1,B3*0.15)) |
| 3 | $250 | =IF(INT(B4/100)=0,B4*0.05,IF(INT(B4/100)=B4/100,B4*0.1,B4*0.15)) |
| 4 | $450 | =IF(INT(B5/100)=0,B5*0.05,IF(INT(B5/100)=B5/100,B5*0.1,B5*0.15)) |
| 5 | $600 | =IF(INT(B6/100)=0,B6*0.05,IF(INT(B6/100)=B6/100,B6*0.1,B6*0.15)) |
The formula =IF(INT(B2/100)=0,B2*0.05,IF(INT(B2/100)=B2/100,B2*0.1,B2*0.15)) works as follows:
- The formula divides the sale amount in cell B2 by 100, and then applies the INT function to get the integer part of the quotient. For example, if B2 contains $75, the formula returns 0.
- The formula then uses the IF function to check if the result of the INT function is 0. If it is, it means that the sale amount is less than $100, and the commission is 5% of the sale amount. For example, if B2 contains $75, the formula returns $3.75, which is 5% of $75.
- If the result of the INT function is not 0, the formula then uses another IF function to check if the result of the INT function is equal to the original quotient. If it is, it means that the sale amount is an integer multiple of 100, and the commission is 10% of the sale amount. For example, if B2 contains $150, the formula returns $15, which is 10% of $150.
- If the result of the INT function is not equal to the original quotient, it means that the sale amount is more than $500, and the commission is 15% of the sale amount. For example, if B2 contains $600, the formula returns $90, which is 15% of $600.
The table below shows the results of the formula for each sale:
| A | B | C |
|---|---|---|
| Sale | Amount | Commission |
| 1 | $75 | $3.75 |
| 2 | $150 | $15 |
| 3 | $250 | $25 |
| 4 | $450 | $45 |
| 5 | $600 | $90 |
Other Approaches
There are other ways to use the INT function with the IF function in Excel, depending on your needs and preferences. Here are some examples:
- You can use the ROUND function instead of the INT function to round the number to the nearest integer, rather than discarding the fractional part. For example,
=IF(ROUND(B2,0)=B2,"Even","Odd")will return “Even” if the number in cell B2 is even, and “Odd” otherwise. - You can use the MOD function instead of the INT function to get the remainder of dividing the number by 2, rather than getting the integer part. For example,
=IF(MOD(B2,2)=0,"Even","Odd")will return “Even” if the number in cell B2 is divisible by 2, and “Odd” otherwise. - You can use the CHOOSE function instead of the nested IF functions to return a value based on the integer part of the number. For example,
=CHOOSE(INT(B2/100)+1,B2*0.05,B2*0.1,B2*0.15)will return the commission for the sale amount in cell B2, based on the same rules as before.
