Ignoring #N/A in sorting?

[Arsenio Oloroso]

I have a multipage workbook in which I need to average the 15 highest values
in the last column for each sheet. Not all the cells in this column-which
contain the results of a calculation in two other columns--are filled in. I
recorded a macro that sorts the sheet and then averages the figures in the
top 15 cells of the last column. Problem arises for any sheet in which all
the cells in that last column aren't filled in. Excel sorts and then puts
all the #N/A cells at the top. How do I get the macro to ignore any cells
that don't contain values?



Thanks for any leads on this.



Arsenio

 

[Dave Peterson]

I don't think you can have excel ignore anything in the range to be sorted.

But maybe you could modify your formula to return something besides #n/a -- or
even insert a new column that you can use to return what you want if there's an
error in that cell.

=if(iserror(x2),99999999,x2)
copy down the column
and sort your data based on this column.