Ignoring errors in a range of data and Filtering

khadert

New Member
Joined
Feb 19, 2007
Messages
14
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top