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.

 

[hsandeep]

If you replace with 1 it will give the smallest - largest number.

So how to fill the corresponding cell in K4:V4 with 4 if the minimum is found in K2:V2 (minimum must not be 0 or "" blank)

 

[AhoyNC]

You mean like this?

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

Cell Formulas
Range	Formula
AA2	AA2	=IFERROR(SMALL(IF(K2:V2<>0,IF(K2:V2<>"",K2:V2)),1),0)
AB2	AB2	=IFERROR(LARGE(IF(K2:V2<>0,IF(K2:V2<>"",K2:V2)),1),0)
K4:V4	K4	=IF($AA$2<>0,IF(K2=$AA$2,$AA$2,""),"")

 

[hsandeep]

You mean like this?

Not exactly....in K4 I don't want to use $AA$2 instead I would prefer using the range $K$2:$V$2....K4 the formula needs to be modified

 

[AhoyNC]

Using Fluff's formula try:
Drag formula across columns.

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	0	10	6	7	14	15	9		5	4	20
3
4										4
Sheet3

Cell Formulas
Range	Formula
K4:V4	K4	=IFERROR(IF(AGGREGATE(15,6,$K$2:$V$2/($K$2:$V$2<>0),1)=K2,K2,""),"")

 

[Peter_SSs]

Not exactly....in K4 I don't want to use $AA$2 instead I would prefer using the range $K$2:$V$2....K4 the formula needs to be modified

Like this?

24 02 21.xlsm
	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	0	10	6	7	14	15	9		5	4	20
3
4										4
Min

Cell Formulas
Range	Formula
K4:V4	K4	=IF(K2=AGGREGATE(15,6,$K2:$V2/($K2:$V2<>0),1),K2,"")

 

[hsandeep]

Like this?

Sir, Extremely sorry for late reply. I am not getting any link on the email...Sorry Sir. Yes, this is exactly what I require. Thanks Sir

 

[Peter_SSs]

You are welcome.