Auto Filter numeric values.

[George Wilson]

I am currently using Excel 2003 but have tried this in XP
and get the same behavior. I have a column marked as
numeric with 2 decimal places and values similar to format
1.00, 1.10, 2.00, 2.20.... I am trying to filter out with
a custom filter all the *.00 numbers. I have tried this
using the * and ? wildcards, I have tried ends with,
equals and contains, no matter how I try the filter I'm
not getting expected results. Am I filtering this the
wrong way or is this a filter bug?
TIA
George

 

[Frank Kabel]

Hi George
try the following:
- add a helper column
- insert the formula
=MOD(A1,1)
and copy down for all rows
- now filter all zero values for this column

 

[AlfD]

Hi!

You say your numbers are formatted to 2d.p. But what are their actua
values? Check one out: does it really read 2.00 when not constrained b
formatting?


1.95 <= 2.00 < 2.05. That is, to find 2.00 you need to look at a range


So, to find one such value is fairly straightforward.

To find any (apparently) ending in .00 is trickier.

There is a way using =A1-int(A1) which isolates the decimal bit of A1
If you put this in a helper column you could run a single search on th
relevant range.

Al

 

[AlfD]

Frank:

Are you sure they will actually equal zero and not, for example, 0.1?

Al

 

[AlfD]

George:

A post script.

1. You seem to have been treating these numbers as strings. A numbe
isn't stored as a string of its decimal digits and certainly not it
screen-formatted version.

2. It divides the fire power available to help you if you double-post.

Al

 

[Frank Kabel]

Hi
if I understood the OP correctly this should display zero for all
numbers without a decimal value

 

[Dave Peterson]

Another option:

use a helper column of cells with a formula like:

=text(a2,"#.00")

This is just text. Then you can filter on that column with Contains .00