MIN
 

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

MIN
 

One possible way

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



--


Regards,


Peo Sjoblom




"Daniel Q." wrote in message
...
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

MIN
 

See if this array formula works for you:

=MIN(IF(U11:U400,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


"Daniel Q." wrote:

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

MIN
 

The follow array formula should get you started:

"Daniel Q." wrote in message
...
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

MIN
 

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)

"Daniel Q." wrote in message
...
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