CODEWITHSUNDEEP
excelexcel-formula
Jeff Brady
Jeff Brady

Reputation: 1498

How I do exclude 0 when using MIN with an IF?

I have a workbook with 2 worksheets, Sheet1 and Sheet2.

Sheet1 has a value (product code) to look up, and an empty cell to fill:

A      B
A100
A200
B150
C3AB

Sheet2 has a bunch of product codes and costs:

A      B
A100   35
A100   14
A100    0
A200   10
A200   12
etc, etc, etc

I'm using the following formula in Sheet1 B1 to find the MIN matching value from Sheet2:

=MIN(IF(Sheet2!$A$1:$A$5=A1,Sheet2!$B$1:$B$5))

How can I also exclude returning a match of 0? In the case of product A100, I would want to return the lowest non-zero match which would be 14.

Upvotes: 3

Views: 7780

Answers (4)

Jacob Bolda
Jacob Bolda

Reputation: 582

Would it be any faster if you add column C on Sheet 1 and fill it with:

=IF(B1=0," ",B1)

Then your original formula entered as a matrix would work referencing column C instead:

=MIN(IF(Sheet2!$A$1:$A$5=A1,Sheet2!$C$1:$C$5))

Can anyone comment on the speed of adding another column? It sounds like the sheet has up to ~20000 rows.

Upvotes: 2

Jüri Ruut
Jüri Ruut

Reputation: 2530

Array formula: 

{=SMALL(IF(Sheet2!$B$1:$B$5<>0;IF(A3=Sheet2!$A$1:$A$5;Sheet2!$B$1:$B$5;"");"");1)}

Shift-Ctrl-Enter in formula window to insert, curled brackets are inserted by Excel, not by a user.

MINbehaved strangely, issuing 0also for non-numeric values in the array. SMALL was used instead, giving #NUM! error for non-existing references.

Example spreadsheet: http://www.bumpclub.ee/~jyri_r/Excel/MInimal_value_excluding_0.xls

Upvotes: 1

scott
scott

Reputation: 2275

Try submitting this as an array formula in B1

untested

=MIN(IF(((Sheet2!$A$1:$A$5=A1)*(Sheet2!$B$1:$B$5>0))>0,Sheet2!$B$1:$B$5))

I think This Will work

Upvotes: 2

Sixhobbits
Sixhobbits

Reputation: 1528

AFAIK: Either use VBA to loop through the values, or:

create another column using a formula similar to =IF(a1=0,"",A1) (copied down for each value in column A). Then you can use the min function on this column.

HTH

Upvotes: 0

Related Questions