min if

[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

 

[JoeU2004]

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

 

[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

 

[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

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

 

[JoeU2004]

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

=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

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

 

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

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

=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

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

 

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

=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