Conditional MIN value
 

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.

Conditional MIN value
 

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

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.

Conditional MIN value
 

Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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.

Conditional MIN value
 

Sorry, missed a bracket from the end:

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

Pete

On Dec 8, 2:00 am, Pete_UK wrote:
Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 -

Conditional MIN value
 

In ,
Pete_UK spake thusly:

Sorry, missed a bracket from the end:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 wrote:
Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 -

Conditional MIN value
 

In , RobN
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
...

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.

Conditional MIN value
 

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 ,
Pete_UK spake thusly:

Sorry, missed a bracket from the end:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 wrote:
Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 -

Conditional MIN value
 

In ,
Pete_UK 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 ,
Pete_UK spake thusly:

Sorry, missed a bracket from the end:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 wrote:
Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 -

Conditional MIN value
 

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 ,
Pete_UK 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 ,
Pete_UK spake thusly:

Sorry, missed a bracket from the end:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 wrote:
Try this array* formula:

=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="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 -

In essence, writing a formula that returns the
 

Hello Pete. I saw your recent help which relates to my current qestion.

In essence, writing a formula that returns the:
Sum
Average
Min
Max
Standard Deviation
If a value in a dataset fromn one sheet matches a value in another dataset. Thanks