# Ignoring errors in a range of data and Filtering

##### New Member
Morning All (its morning in Australia),

Im trying to filter a range of data to only show the rows relating to the top 5 values in column W. The formula im using in Excel in conjunction with an advanced filter is as follows:

=IF(OR(W18 = LARGE(\$W\$18:\$W\$62, 1), (W18 = LARGE(\$W\$18:\$W\$62, 2)), (W18 = LARGE(\$W\$18:\$W\$62, 3)), (W18 = LARGE(\$W\$18:\$W\$62, 4)), (W18 = LARGE(\$W\$18:\$W\$62, 5))), TRUE, FALSE)

This works fine though gives an error if any of the values in the range W18:W62 is an error. I am unable to remove all errors in this range (as inserting an ISERROR() in the formula within these cells will make the formulas too long).

Is there a way of having the above formula ignore the cells/rows within W18:W62 that contain errors.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

##### MrExcel MVP
Morning All (its morning in Australia),

Im trying to filter a range of data to only show the rows relating to the top 5 values in column W. The formula im using in Excel in conjunction with an advanced filter is as follows:

=IF(OR(W18 = LARGE(\$W\$18:\$W\$62, 1), (W18 = LARGE(\$W\$18:\$W\$62, 2)), (W18 = LARGE(\$W\$18:\$W\$62, 3)), (W18 = LARGE(\$W\$18:\$W\$62, 4)), (W18 = LARGE(\$W\$18:\$W\$62, 5))), TRUE, FALSE)

This works fine though gives an error if any of the values in the range W18:W62 is an error. I am unable to remove all errors in this range (as inserting an ISERROR() in the formula within these cells will make the formulas too long).

Is there a way of having the above formula ignore the cells/rows within W18:W62 that contain errors.

Y18:

Control+shift+enter...

=IF(ROWS(\$Y\$18:Y18)<=5,LARGE(IF(ISNUMBER(\$W\$18:\$W\$62),\$W\$18:\$W\$62),ROWS(\$Y\$18:Y18)),"")

and copy down.

##### New Member
Thanks for the reply Aladdin though i cant seem to get it to work properly

Using your formula and copying it into column Y gives me the value of the 5 highest cells in column W (with the result being placed in cells Y18:Y22).

However what i want is to filter out all except the rows coresponding to the 5 highest values within column W. Column W represents an overall risk score based on the information placed in preceding columns. I want the user to be able to filter out the rows coresponding to the top 5 risks.

Thanks again.

Replies
2
Views
81
Replies
14
Views
210
Replies
10
Views
104
Replies
13
Views
120
Replies
5
Views
247

1,191,388
Messages
5,986,321
Members
440,017
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back