Get second smallest & second largest from a range

[hsandeep]

My range is K2:V2 which generates values including 0 and also “” (blank)
I need formulas:
AA2=Second smallest non-zero non blank number from K2:V2 else 0
AB2= Second largest non-zero non blank number from K2:V2 else 0

How to accomplish?
Thanks in advance.

 

[awoohaw]

Do you want a value of zero to be considered in the function as a number?

 

[hsandeep]

Do you want a value of zero to be considered in the function as a number?

No

 

[Coyotex3]

Maybe something like this:

=LARGE(K2:V2,2)

=SMALL(K2:V2,2)

 

[AhoyNC]

Try:
These are array formulas and must be entered with CTRL-SHIFT-ENTER

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V	W				AA	AB
1	1	2	3	4	5	6	7	8	9	10	11	12
2	0	10	6	7	14	15	9		5	4	20						5	15
Sheet2

Cell Formulas
Range	Formula
AA2	AA2	=IFERROR(SMALL(IF(K2:V2<>0,IF(K2:V2<>"",K2:V2)),2),0)
AB2	AB2	=IFERROR(LARGE(IF(K2:V2<>0,IF(K2:V2<>"",K2:V2)),2),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	1	2	3	4	5	6	7	8	9	10	11	12
2	0	10	6	7	14	15	9		5	4	20			5	15
Master

Cell Formulas
Range	Formula
R2,V2	R2	=""
X2	X2	=AGGREGATE(15,6,K2:V2/(K2:V2<>0),2)
Y2	Y2	=LARGE(K2:V2,2)

 

[awoohaw]

@Fluff
I keep forgetting to divide by the edit on the SMALL function in AGGREGATE, Thanks for having me see this reminder.

I do have a question, how do you account if there are no positive numbers in the LARGE function, the zero is counted here:

Book1
	A	B	C	D
8	-2	-1	0	-1
Sheet1

Cell Formulas
Range	Formula
D8	D8	=LARGE(A8:C8,2)

 

[Fluff]

how do you account if there are no positive numbers in the LARGE function, the zero is counted here:

Good point, if that is a possibilty, then I'd use aggregate again.

 

[hsandeep]

AhoyNC
The number 2 used in the formulas, is it for the "second". So, if I replace 2 with 1 in the formulas, will it be giving smallest & largest respectively?

 

[AhoyNC]

Yes the number 2 is for second. If you replace with 1 it will give the smallest - largest number.
Fluff's formula does not require CTRL-SHIFT-ENTER, but you need to add the IFERROR function to his formula if all the cells are blank and you want to return 0.