non-zero MIN (Excel 2003)

[tfboni]

I want a cell to indicate the non-zero minimum of a group of non-contiguous cells in the same row. (Some of the cells are zero values.)

For instance I want cell G3 to display the non-zero minimum of the values in cells A3, C3, & E3.

[HansV]

You could use an array formula like this (confirm with Ctrl+Shift+Enter instead of just Enter):

=MIN(IF((A3:F3<>0)*(MOD(COLUMN(A3:F3),2)=1),A3:F3) )

[tfboni]

Thanks for the quick reply!
I'll try it.
Does it matter that I have other number-containing cells "like such as" at B3, D3, and F3 that shouldn't be included?

[HansV]

The condition MOD(COLUMN(A3:F3),2)=1 causes only cells with an odd column number to be included; cells with an even column number are ignored.

Explanation:
COLUMN(...) returns the column number of a cell.
MOD(COLUMN(...),2) returns the remainder of the column number after division by 2, i.e. 0 for even column numbers and 1 for odd column numbers.
MOD(COLUMN(A3:F3),2)=1 is true if and only if the column number is odd.

The other condition A3:F3<>0 ensures that only non-zero values are included.

[tfboni]

Thanks for the clear explanation.

I wasn't familiar with the COLUMN function...I'm guessing there's also a ROW function...

I guess I also could create a copy of only the odd columns (so that they would be contiguous) but I was trying to avoid that...
I'll let you know how it works out after I get back to the office.

Again, thanks for your help!

[HansV]

Yep, there is also a ROW function that returns the row number of the argument (or of the cell containing the formula, if no argument is provided).
If you look up either of these functions in the Excel help, the See Also link will mention the other.

[tfboni]

Hans,

I tried your formula and it does the trick!
Thanks very much for your assistance with this.

Regards,

Tom