min if

R

Rene

MIN(IF(A2:A43="d",B2:BG43)) entered as an array

Does it return a 0 because of the blank cell? And how do I ignore the blank
cell?

a2 b2 c2
d blank 120
d 100
d 150 175
a 75 90

Thanks
 
J

JoeU2004

Rene said:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
[....] how do I ignore the blank cell?

You probably already discovered that the "obvious" solution does __not__
work, namely the following array formula:

=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43))

But the following array formula expresses the same logic:

=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))


----- original message -----
 
B

Bernard Liengme

Working with just your smaller data set I used
=MIN(IF(A2:A5="d",IF(B2:C5>0,B2:C5)))
and got 100 as expected
Of course it must be array entered with CTRL+SHIFT+ENTER
best wishes
 
R

Rene

=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43)) This returns the min in
B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43.

Thanks

JoeU2004 said:
Rene said:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
[....] how do I ignore the blank cell?

You probably already discovered that the "obvious" solution does __not__
work, namely the following array formula:

=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43))

But the following array formula expresses the same logic:

=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))


----- original message -----

Rene said:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array

Does it return a 0 because of the blank cell? And how do I ignore the
blank
cell?

a2 b2 c2
d blank 120
d 100
d 150 175
a 75 90

Thanks
 
J

JoeU2004

Rene said:
=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43))
This returns the min in B2:BG43. I'm looking for the min
in B2:BG43 with a "d" in A2:A43.

You misunderstood. I said that formula does __not__ work.

You should use the other formula:

=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))

I presented the first formula (with AND) to help you understand the second
formula.


----- original message -----

Rene said:
=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43)) This returns the min in
B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43.

Thanks

JoeU2004 said:
Rene said:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
[....] how do I ignore the blank cell?

You probably already discovered that the "obvious" solution does __not__
work, namely the following array formula:

=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43))

But the following array formula expresses the same logic:

=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))


----- original message -----

Rene said:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array

Does it return a 0 because of the blank cell? And how do I ignore the
blank
cell?

a2 b2 c2
d blank 120
d 100
d 150 175
a 75 90

Thanks
 
J

JoeU2004

PS....
You should use the other formula:
=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))

Alternatively:

=MIN(IF(A2:A43="d",IF(B2:BG43<>"",B2:BG43)))

That might be easier to understand. However, beware that that does not
scale well due to nesting limits before Excel 2007. That is, the number of
ANDed conditions is limited to 7, and it is difficult to combine ANDed and
ORed conditions.


----- original message -----

JoeU2004 said:
Rene said:
=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43))
This returns the min in B2:BG43. I'm looking for the min
in B2:BG43 with a "d" in A2:A43.

You misunderstood. I said that formula does __not__ work.

You should use the other formula:

=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))

I presented the first formula (with AND) to help you understand the second
formula.


----- original message -----

Rene said:
=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43)) This returns the min in
B2:BG43. I'm looking for the min in B2:BG43 with a "d" in A2:A43.

Thanks

JoeU2004 said:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
[....] how do I ignore the blank cell?

You probably already discovered that the "obvious" solution does __not__
work, namely the following array formula:

=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43))

But the following array formula expresses the same logic:

=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))


----- original message -----

MIN(IF(A2:A43="d",B2:BG43)) entered as an array

Does it return a 0 because of the blank cell? And how do I ignore the
blank
cell?

a2 b2 c2
d blank 120
d 100
d 150 175
a 75 90

Thanks
 
J

John

PS....
You should use the other formula:
=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))

Alternatively:

=MIN(IF(A2:A43="d",IF(B2:BG43<>"",B2:BG43)))

That might be easier to understand.  However, beware that that does not
scale well due to nesting limits before Excel 2007.  That is, the number of
ANDed conditions is limited to 7, and it is difficult to combine ANDed and
ORed conditions.

----- original message -----


You misunderstood.  I said that formula does __not__ work.
You should use the other formula:

I presented the first formula (with AND) to help you understand the second
formula.
----- original message -----
Rene said:
=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43)) This returns the minin
B2:BG43.  I'm looking for the min in B2:BG43 with a "d" in A2:A43.
Thanks
:
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
[....] how do I ignore the blank cell?
You probably already discovered that the "obvious" solution does __not__
work, namely the following array formula:
=MIN(IF(AND(A2:A43="d",B2:BG43<>""),B2:BG43))
But the following array formula expresses the same logic:
=MIN(IF((A2:A43="d")*(B2:BG43<>""),B2:BG43))
----- original message -----
MIN(IF(A2:A43="d",B2:BG43)) entered as an array
Does it return a 0 because of the blank cell? And how do I ignore the
blank
cell?
a2 b2          c2
d   blank    120
d   100
d   150      175
a     75        90
Thanks

Hi - I was not part of this discussion, but your thread has just
helped me A LOT - massively saved face at my client - thanks for your
help all!

JohnOnTheInternet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top