Small function clarification & help

[SimonHow]

Hi all

I am using the "Large" and "Small" to analyse some data.

I have around 250 lines of numeric data, that I want to find the largest (max) smallest (min) values, I am using the max and min functions with no problem. I also need to find a notional max and min where I remove any extreme peaks. I can do this with the large and small functions. The large function with k set to 2 (=Large(A1:A250,2), removes the true max, so works perfectly

The small function is not performing as expected. Where there a lot of 0's in the data, I expected =small(A1:A250,2) to show me the smallest value above 0, but it just shows 0. I have to set k to 51 before it shows a value. I have checked and there are 50 lines where the data is set to 0. Is this what I should expect ? if so is there another way to essentially eliminate the lowest point in the data set and return the next lowest datapoint ? there is a lot of variability on the possible data sets to analyse in this way, hence using the small function.

Many thanks in advance

Simon

 

[Muhammad_Usman]

1235.xlsm
	A	B	C	D
1	1
2	2		1
3	3
4	5
5	6
6	0
7	5
8	0
9	6
10	0
11
Sheet7

Cell Formulas
Range	Formula
C2	C2	=IFERROR(MINIFS(A1:A10, A1:A10, ">"&0), "No value > 0")

 

[SimonHow]

Thanks, however the minimum value in another dataset might not be 0, so this formula will not work for my needs. Thanks anyway

 

[Fluff]

How about

=MIN(FILTER(A2:A250,A2:A250<>MIN(A2:A250)))

 

[SimonHow]

Thanks Fluff, I will see if I can make that work.

Shame about the best result we can now achieve is 3rd