# Thread: non-zero MIN (Excel 2003)

1. ## non-zero MIN (Excel 2003)

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.

2. ## Re: non-zero MIN (Excel 2003)

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) )

3. ## Re: non-zero MIN (Excel 2003)

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?

4. ## Re: non-zero MIN (Excel 2003)

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.

5. ## Re: non-zero MIN (Excel 2003)

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.

6. ## Re: non-zero MIN (Excel 2003)

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.

7. ## Re: non-zero MIN (Excel 2003)

Hans,

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

Regards,

Tom

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•