MIN

[Guest]

I have this formula:
=MIN(U11:U40,H7)+P8

how do i fix it so that it will take the min of the array but not a 0 (zero)
value? For example the formula should give me 2.20 and not zero as the
example shows.
U
11 5.15
12 3.14
13 0
14 2.20
15 7.90
16 10.00
17 0

Thanks in advance.

DSQ

 

[Peo Sjoblom]

One possible way

=IF(H7=0,SMALL(U11:U40,COUNTIF(U11:U40,0)+1),MIN(H7,SMALL(U11:U40,COUNTIF(U11:U40,0)+1)))+P8



--


Regards,


Peo Sjoblom

 

[Guest]

See if this array formula works for you:

=MIN(IF(U11:U40>0,U11:U40,""),H7)+P8

Note: Array formulas must be entered with CTRL-SHIFT-ENTER rather than just
Enter. If done properly, the formula should be enclosed in { }.

HTH,
Elkar

 

[Chip Pearson]

The follow array formula should get you started:

 

[Chip Pearson]

The following array formula should get you started.

=MIN(IF(A1:A10<>0,A1:A10,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the Formula Bar
enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)