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

"Dallman Ross" <dman@localhost.> wrote in message
news:fjcrln$t1c$(E-Mail Removed)...
>
> 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.
>
>

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

On Dec 8, 1:23 am, Dallman Ross <dman@localhost.> wrote:
> 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.

Sorry, missed a bracket from the end:

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

Pete

On Dec 8, 2:00 am, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Dec 8, 1:23 am, Dallman Ross <dman@localhost.> wrote:
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

In <bdd0a728-7166-41bc-8bcd-(E-Mail Removed)>,
Pete_UK <(E-Mail Removed)> spake thusly:

> Sorry, missed a bracket from the end:
>
> =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K1000="SELL"),N1:N1000,10^10))

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

------------------------
> On Dec 8, 2:00 am, Pete_UK <pashu...@auditel.net> wrote:
> > 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
> >
> > On Dec 8, 1:23 am, Dallman Ross <dman@localhost.> wrote:
> >
> >
> >
> > > 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.- Hide quoted text -
> >
> > - Show quoted text -
>

In <(E-Mail Removed)>, RobN <(E-Mail Removed)>
spake thusly:

> There's probably a way to do this with a formula, but another way is to use
> Filters.

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

---------------
> 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
>
> "Dallman Ross" <dman@localhost.> wrote in message
> news:fjcrln$t1c$(E-Mail Removed)...
> >
> > 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.
> >
> >
>
>

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

On Dec 8, 12:40 pm, Dallman Ross <dman@localhost.> wrote:
> In <bdd0a728-7166-41bc-8bcd-7bca5a572...@w34g2000hsg.googlegroups.com>,
> Pete_UK <pashu...@auditel.net> spake thusly:
>
> > Sorry, missed a bracket from the end:
>
> > =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K1000="SELL"),N1:N1000,10^10)-)
>
> 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
>
> ------------------------
>
>
>
> > On Dec 8, 2:00 am, Pete_UK <pashu...@auditel.net> wrote:
> > > 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
>
> > > On Dec 8, 1:23 am, Dallman Ross <dman@localhost.> wrote:
>
> > > > 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.- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

In <e3d23863-b849-430a-b573-(E-Mail Removed)>,
Pete_UK <(E-Mail Removed)> spake thusly:

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

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

------------------------------------------
> 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
>
> On Dec 8, 12:40 pm, Dallman Ross <dman@localhost.> wrote:
> > In <bdd0a728-7166-41bc-8bcd-7bca5a572...@w34g2000hsg.googlegroups.com>,
> > Pete_UK <pashu...@auditel.net> spake thusly:
> >
> > > Sorry, missed a bracket from the end:
> >
> > > =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K1000="SELL"),N1:N1000,10^10)-)
> >
> > 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
> >
> > ------------------------
> >
> >
> >
> > > On Dec 8, 2:00 am, Pete_UK <pashu...@auditel.net> wrote:
> > > > 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
> >
> > > > On Dec 8, 1:23 am, Dallman Ross <dman@localhost.> wrote:
> >
> > > > > 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.- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>

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

On Dec 8, 3:31 pm, Dallman Ross <dman@localhost.> wrote:
> In <e3d23863-b849-430a-b573-48f7c936e...@b40g2000prf.googlegroups.com>,
> Pete_UK <pashu...@auditel.net> spake thusly:
>
> > 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.
>
> 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
>
> ------------------------------------------
>
>
>
> > 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
>
> > On Dec 8, 12:40 pm, Dallman Ross <dman@localhost.> wrote:
> > > In <bdd0a728-7166-41bc-8bcd-7bca5a572...@w34g2000hsg.googlegroups.com>,
> > > Pete_UK <pashu...@auditel.net> spake thusly:
>
> > > > Sorry, missed a bracket from the end:
>
> > > > =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K1000="SELL"),N1:N1000,10^10)--)
>
> > > 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
>
> > > ------------------------
>
> > > > On Dec 8, 2:00 am, Pete_UK <pashu...@auditel.net> wrote:
> > > > > 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
>
> > > > > On Dec 8, 1:23 am, Dallman Ross <dman@localhost.> wrote:
>
> > > > > > 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.- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -