List Only Whole Numbers From Decimal Numbers

[lyambor]

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

[AAE]

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.

[Tony T]

I use something like:

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

[lyambor]

Many thanks - this worked perfectly
HAve a wonderful weekend

[lyambor]

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


HAve a wonderful weekend