Looking for formula for lowest costs, excluding zero

[Guest]

I found a formula for determining the lowest costs from several columns, but
I cannot get it to work in my worksheet. This is nearly 1000 lines long. I
want to search 4 columns and get the lowest cost excluding 0. Any
suggestions would be greatly appreciated. Thanks!!

 

[KL]

Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter):

=MIN(IF(A11000,A11000))

Regards,
KL

 

[Guest]

I tried the formula with the array, but it still did not work. The formula
that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5)))
Do you think this is correct?

 

[KL]

Hi,

Your formula makes no sense to me, can you explain what you are trying to
achieve and which columns you want to evaluate.

REgards,
KL

 

[KL]

Maybe this:

=MIN(IF((MOD(COLUMN(B5:G5),3)=1)*(B5:G5<>0),B5:G5))

Enter as ARRAY formula

Regards,
KL

 

[Guest]

I have a large quote that I am working on and have pricing from several
vendors. I am nearly complete and would like to create a formula to
determine the lowest cost per line. The costs appear in lines H, L, T & U.
I don't want zero to become a part of the lowest pricing. Thanks KL!!!

 

[Cutter]

If you want to determine only the lowest cost for each column (and no
the number of times the lowest cost appears) then try this:

=IF(MIN(H2:H200)>0,SMALL(H2:H200,COUNTIF(H2:H200,0)+1),MIN(H2:H200))

This example is for column H (range H2:H200) so adjust as necessary an
then copy it to to your other columns.

It is not an array formula so just enter in normal wa

 

[Guest]

Thanks... It works great!! I really appreciate your help