I have a quotation form I use which has several supplier values in a column.
I want to calculate the minimum value but because every supplier does not
always
give a quote their value will show zero. In short I want the minimum in the
column
but excluding zero. How do I express this?


--
kinsey

=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)
Assumes your values are in column A between rows 1 and 100, adjust according
to your sheet setup.

This came from:
http://www.ozgrid.com/Excel/minimum-without-zero.htm

Enjoy!

"kinsey" wrote:

I have a quotation form I use which has several supplier values in a column.
I want to calculate the minimum value but because every supplier does not
always
give a quote their value will show zero. In short I want the minimum in the
column
but excluding zero. How do I express this?


--
kinsey

This works a treat. Many thanks.
--
kinsey


"JLatham" wrote:

=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)
Assumes your values are in column A between rows 1 and 100, adjust according
to your sheet setup.

This came from:
http://www.ozgrid.com/Excel/minimum-without-zero.htm

Enjoy!

"kinsey" wrote:

I have a quotation form I use which has several supplier values in a column.
I want to calculate the minimum value but because every supplier does not
always
give a quote their value will show zero. In short I want the minimum in the
column
but excluding zero. How do I express this?


--
kinsey

Question: If my data is NOT in a contine cell, how can I run the formula? I can't get the Non-Continue Cell formula to work.

This formula does not work if the data cells are NOT continue:

=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

What if my data are in cell A2, A6, C7, E9

How can I look up the Min function for these 4 cells: A2, A6, C7, E9 and exclude Zero ?

I need to look up the smallest value for these 4 cells while exclude zero.

Please help.