Minimum greater than 0

[Mel Smith]

I am using the following to find the minimum value in a range: =MIN(AM4:AM63)
How can I amend this to give me the minimum value which is greater than 0? In other words, to exclude all zero values to find the positive value minimum.

Many thanks.

Mel

 

[jasonb75]

Perhaps

=MINIFS(AM4:AM63,AM4:AM63,">0")

 

[lrobbo314]

Book1.xlsx
	A	B	C	D
1	Number		MINIF	CSE
2	0		1	1
3	1
4	2
5	3
MZ

Cell Formulas
Range	Formula
C2	C2	=MINIFS(A2:A5,A2:A5,">0")
D2	D2	=MIN(IF(A2:A5>0,A2:A5))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Mel Smith]

Perfect! Thank you

Mel

 

[Mel Smith]

Jasonb75 gave me the ideal solution. Thanks lrobbo314

 

[Rahul1987]

How abt?

EMT1303-1306.xlsx
	T	U	V
6	Data		Output
7	50		16
8	312
9	0
10	138
11	27
12	186
13	20
14	125
15	158
16	0
17	138
18	219
19	331
20	16
21	20
22	244
23	291
24	296
1303

Cell Formulas
Range	Formula
V7	V7	=SMALL(IF(T7:T24>0,T7:T24),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mel Smith]

Thanks, Rahul. Jasonb75 gave me a solution that I’m working with but it’s nice to have an alternative as backup.
Mel

 

[jasonb75]

@Rahul1987

=AGGREGATE(15,6,T7:T24/(T7:T24>0),1)

While some people do still use them, SMALL and LARGE are only really needed for compatibility with excel 2007 or older.
Using AGGREGATE without IF also removes the need to use Ctrl Shift Enter.