Min of Positive Values

**Edward C** · 07-22-2009, 10:17 PM

I want cell D5 to be the minimum value of cells D29:T29. Some of the cells will have no value (or $0.00) and some will have values in them. I tried the following formula in cell D5 but it doesn't work: =IF(D29:T29>0,MIN(D29:T29))).

Thanks

**Edward C** · 07-22-2009, 10:33 PM

I want cell D5 to show the minimum value of cells D29:T29. Some of the cells in D29 to T29 will have $0.00 values and others will have values. I tried the following formula and it gives me a $0.00 value in cell D5:
=IF(D29:T29>0,MIN(D29:T29)))

**D_Rennie** · 07-22-2009, 11:33 PM

try
=MIN(IF(D29:T29>0,D29:T29))

**shg** · 07-22-2009, 11:34 PM

=MIN(IF(D29:T29>0, D29:T29) )

The formula must be confirmed wit Ctrl+Shift+Enter rather than just Enter.

**D_Rennie** · 07-22-2009, 11:37 PM

try
=MIN(IF(D29:T29>0,D29:T29))

Or =MIN(IF(D29:T29=0,"",D29:T29))

**shg** · 07-22-2009, 11:38 PM

Threads merged.

Edward, please read the forum rules before posting again, including both duplicate posts and thread titles (which I have edited)

**Edward C** · 07-23-2009, 12:15 AM

Sorry guys, I wasn't sure if I posted it in the correct thread. I'm kinda new at this. I did get the following formula to work though: =SMALL(D29:T29,COUNTIF($D$29:$T$29,0)+1). I tried the one you suggested but since I have merged cells, it won't allow an array formula. Now I have a different problem. I'm not quite sure how to explain it so I'll try to put together an illustration and put it in my next reply. Hope someone can help with it. I'm not sure if it can be accomplished. I appreciate your help so far though.

**Edward C** · 07-23-2009, 12:53 AM

I'm attaching a spreadsheet for illustration. I want the vendor associated with the lowest cost to appear in cell A3. I created a short list at the bottom for possibly a vlookup but I don't know if it will work.

**DonkeyOte** · 07-23-2009, 02:25 AM

Per your file:

A3: =INDEX($C$6:$K$6,MATCH($E$3,$D$27:$K$27,0))

Do not use Merged Cells -- for Horizontal ranges you can use "Centre Across Selection" to get the same effect without any of the associated headaches.

On an aside the SMALL(...,COUNTIF()+1) approach is certainly one I would opt for in preference to a MIN array regardless of Merged Cells issues.