Access Excel INT Function to get the integer of number

This Access / Excel tutorial explains how to use INT Function to get the integer part of a number.

Access Excel INT Function to get the integer of number

When you have a number with decimal places, you can separate the number into integer part using INT Function, and then work around to get the decimal part. INT Function can be used in Access and Excel, and also VBA.

Syntax of INT Function

INT(number)

INT Function only contains one argument, which is the number from which you want to get the integer part.

The syntax for Access, Excel and VBA are same.

Example of INT Function

Example for positive numbers

Formula Result Explanation
INT(11.4) 11 Integer part of 11.4 is 11
INT(11.6) 11 Integer part of 11.6 is 11 also ,regardless whether the decimal part is over 0.5
11.6-INT(11.6) 0.6 Return the decimal part of 11.6

 

Unlike positive number, handling of negative number is a little bit confusing, it returns the first whole number less than your input number. For example, -5.1 would become -6. In the below examples, I will demonstrate how to return 5 or -5 instead of -6.

Formula Result Explanation
INT(-5.1) -6 The first whole number less than -5.1 is -6
INT(abs(-5.1)) 5 To return the true integer part using absolute function
IF(A1<0,-INT(ABS(A1)),INT(ABS(A1))) -5 Suppose A1 is -5.1, to return the true integer part and then add the sign back

Example of VBA INT Function

INT Function is particularly useful in custom rounding, below is a custom MRound function that I created in my previous post, click here to read more.

Function wMRound(pValue As Double, multiple) As Double
    Dim negNumber As Boolean
    If pValue < 0 Then
        pValue = 0 - pValue
        negNumber = True
    End If
    If multiple < 0 Then
        multiple = 0 - multiple
    End If
    
    Select Case pValue / multiple - Int(pValue / multiple)
        Case Is < 0.5
            result = Int(pValue / multiple) * multiple
        Case Is >= 0.5
            result = Int(pValue / multiple) * (multiple) + multiple
    End Select
        
    If negNumber = True Then
        wMRound = 0 - result
    Else
        wMRound = result
    End If
End Function

Outbound References

https://support.office.com/en-us/article/INT-function-A6C4AF9E-356D-4369-AB6A-CB1FD9D343EF

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *