Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: List Only Whole Numbers From Decimal Numbers

1. Senior Member
Join Date
30th March 2003
Location
USA
Posts
128

## List Only Whole Numbers From Decimal Numbers

From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed

For example

34.95
21.88
21.00
56.00
45.77

Only those valaues ending in .00 will list

I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents

Many thanks

2. ## Re: Delete Numbers That Display Cents

Perhaps you could modify this formula to suit. It assumes two decimal places, however, as far as Excel is concerned if the number in your cell is "25" (whole number), but is formatted to show "25.00" (a decimal), Excel does not recognize the decimal or the "zero" characters after it.

The above explanation is so that you understand that an error value will occur with the formula in the first "IF" function and the assumption is that the value in the cell, for which the error is returned, is a whole number in reality.

=IF(ISERR(TEXT(MID(A4,FIND(".",A4)+1,2),"#")*1),TRUE,IF((TEXT(MID(A4,FIND(".",A4)+1,2),"#")*1)>0,FALSE,A4))
Use this formula in a helper column, then filter on TRUE values to add or FALSE values to delete rows.

Edit: If you use the DSUM function in conjuction with the helper column, you don't need to delete any cells. Give the helper column a header, then use a criteria cell in a top row above the data and reference the crtieria cells when using DSUM.
Last edited by AAE; May 23rd, 2009 at 04:17.

3. Member
Join Date
18th January 2009
Posts
63

## Re: Delete Numbers That Display Cents

I use something like:

=IF(MOD(A1,1)<>0,0,A1)
or
=IF(INT(A1)<>A1,0,A1)

Excel Video Tutorials / Excel Dashboards Reports

4. Senior Member
Join Date
30th March 2003
Location
USA
Posts
128

## Re: Delete Numbers That Display Cents

Many thanks - this worked perfectly
HAve a wonderful weekend

5. Senior Member
Join Date
30th March 2003
Location
USA
Posts
128

## Re: Delete Numbers That Display Cents

Thank you - I would never have worked this equation up
it is great and I can use it for many different things

HAve a wonderful weekend

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno