Conditional MIN value

  • Thread starter Thread starter Dallman Ross
  • Start date Start date
D

Dallman Ross

I'm stuck and could use some help. I have a table with lots of
columns, one of which -- N -- is a price. I want to find the
minimum price where the value in Column A is "HOV" and Col. B is
"STK" and Col. K is "SELL".

Thanks for the quick hand at this, guys.
 
There's probably a way to do this with a formula, but another way is to use
Filters.
I think this should work....
Apply filter to those columns then simply filter Column A to show only those
with HOV; filter column B for STK; filter column K for SELL, then filter
column N for the lowest value.

Rob
 
Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K1000="SELL"),N1:N1000,10^10)

All ranges must be the same size - I've assumed you have 1000
elements.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit
it, rather than the normal ENTER. If you do this correctly then Excel
will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Hope this helps.

Pete
 
Sorry, missed a bracket from the end:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K1000="SELL"),N1:N1000,10^10))

Pete
 
In <bdd0a728-7166-41bc-8bcd-7bca5a57266d@w34g2000hsg.googlegroups.com>,
Pete_UK said:
Sorry, missed a bracket from the end:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K1000="SELL"),N1:N1000,10^10))
Click to expand...

Looks promising, Pete, but I'm getting a #NAME? error. I'm thinking the
problem may lie with the "*" between text columns. Don't we need a SUMPRODUCT
in there somewhere, anyway?

Dallman

------------------------
 
RobN <[email protected]> said:
There's probably a way to do this with a formula, but another way is to use
Filters.
Click to expand...

Rob, thanks for the ideas. I need a formula, because I am
performing further operations/calculations on the resulting value.
I do already use filters as you suggested. The formula I use will
be in a separate workbook, by the way. It's not a problem to have
both books open if necessary, though.

Thanks for the input,
dman

---------------
 
No, you don't need a sumproduct - the * is equivalent to AND.
Basically the formula is saying if all three conditions are met, then
take the value from column N otherwise take a very large value, and do
this for every cell in the ranges. Then, with the array built up, take
the minimum of those numbers.

If you are getting #NAME? error, then you may have mis-typed MIN or
IF, or you may have missed the quotes around "HOV", "STK" or "SELL",
or you may have missed the : from between one of the ranges, or missed
one of the brackets.

Don't forget, you must commit the formula with CSE after you amend it.

Hope this helps.

Pete
 
In <e3d23863-b849-430a-b573-48f7c936eb3c@b40g2000prf.googlegroups.com>,
Pete_UK said:
No, you don't need a sumproduct - the * is equivalent to AND.
Basically the formula is saying if all three conditions are met, then
take the value from column N otherwise take a very large value, and do
this for every cell in the ranges. Then, with the array built up, take
the minimum of those numbers.
Click to expand...

I got it to work now! Thanks again, Pete. Not entirely sure what I
did wrong the first time. I will not for those following along that
an errant "-" got intorduced in your correction, though. I took it
out. I also changed "10^10" to "" -- I am content to have nothing there
if there is now price for the item in question.

Very helpful -- thanks!

Dallman

------------------------------------------
 
I'm glad you got it working - thanks for feeding back.

You sometimes get spurious line-breaks with long formulae in the
newsgroups, and sometimes a hyphen gets included, depending on what
you are using to view the posts.

Pete
 
Back
Top