How to find max value but removing outliers first.

Hi,

I have a data set such as [1,2,3,4,5,10,18,19,200]

I want to find the maximum value after removing outliers.

So I want 200 to be not included and to find out what 19 is.

How do I do this?

So, do you mean this:

=LARGE(A1:A9,2)

i.e. the second largest value?

Hope this helps.

Pete

I was looking for a more variable formula that accounts for a large data set where I have no visibility of the max values.

I managed to use MAX and IF statement as an array formula so this problem is now solved.

For those who are interested this is what I used:

{=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values)))}