Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Determining an Integer Value.

Determining an Integer Value

Written by Allen Wyatt (last updated July 9, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021


3

VBA, true to its BASIC roots, provides a function to return the integer value of an expression. This means that anything to the right of the decimal point is truncated. Thus, if a formula would normally result in a value such as 18.73, then the integer value of that formula would result in 18.

The syntax for the integer function is as follows:

x = Int(y)

where x is the result and y is a value or an expression that evaluates to a value.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9931) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Determining an Integer Value.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Printing Only Selected Pages

When you print a worksheet, you don't need to print the whole thing. You can print only the pages you want. Here's how to ...

Discover More

Understanding the POWER Function

The POWER function is used to raise one value to another value. However, Excel also provides an exponentiation operator ...

Discover More

Understanding Operators

At the heart of working with Excel is the process of creating formulas that calculate results based on information within ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!

More ExcelTips (ribbon)

Enforcing Moving Cells Up

When you design your worksheets, you probably want users to interact with those worksheets in specific ways. What ...

Discover More

Engineering Calculations

Need to normalize your data in some way so that all your values are in a given format? This tip presents a number of ...

Discover More

Stopping Fonts from Changing

There are multiple ways that Excel can create a workbook, and the formatting in each creation method can be different. In ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

2024-07-10 08:09:02

Alex Blakenburg

@Kiwerry - in replying I nearly fell for the same trap you did. The tip was a "VBA" tip and in VBA the function is indeed "Fix" ie x = Fix(y)

Ref: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/int-fix-functions
"The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8."

In Excel itself = INT(A1) is the same as VBA int but to remove the decimals from both positive and negative numbers you would use =TRUNC(A1) or =ROUNDDOWN(A1,0). Coincidentally fielded a question on this on the Mr Excel Forum just today.


2024-07-09 08:11:56

Kiwerry

Thanks for pointing that out, Alex.

It seems you meant "...the first integer less than..." rather than "...the first negative integer less than..."

The function you refer to as Fix is FIXED.

Can't read something without looking for "typos" after years of teaching...


2020-07-25 10:14:17

Alex B

The above only covers positive numbers. In the case of negative numbers it does not “truncate” the decimal point, it returns the first negative integer less than or equal to the number.
So while (positive) 18.73 returns 18
(negative) -18.73 returns -19
If you really want both to truncate the decimal, use the Fix function.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.