Catch 22 on “Top 10” auto filter function

I am trying to create a macro which will quickly auto filter and produce a “Bottom 10” list

Problem is that my list has lots of zeroes which I want the filter to ignore.

However if I change the zeros to blank the “Top 10” autofilter function doesn’t work (think it needs a number within each cell)

Is there any way around this?

Thanks in advance

Here is the sample worksheet

As you can see i have a formula in the zero cells which the "bottom 10" function then picks up.

However if i completely remove the forumla's out it does seem to work. However as the formula runs all the way down the column i would prefer to keep formula in and have the function working!

ibenam - Change your formulas to look like this: =IF(ISERROR(D3/E3),"",(D3/E3)) It ignores the "" when filtering on Top 10 and Bottom 10.

Originally Posted by gotchuck

ibenam - Change your formulas to look like this: =IF(ISERROR(D3/E3),"",(D3/E3)) It ignores the "" when filtering on Top 10 and Bottom 10.

Hi

I have tried this previously and it makes the "Top 10" option in autofilter dissapear?

Originally Posted by Izandol

You may use formula:
=IFERROR(D3/E3,FALSE)

and use Conditional Formatting to hide FALSE values.

Perfect - worked!

I never knew you could put False for text as whenever i used plain text without the "" I would always get the #NAME? error

Thanks - Rep added.