FInding Min Value with Criteria

A

AndrewK

Hello there,

I have a problem...

I have a sheet of values and one certain column tells me if the line item is
either a but order or a sell order - nominated by either "True" (for sell
orders) or "False" (for buy orders),

I would like to be able to return the most minimum value of all the "False"
lines within the sheet also on a separate formula return the MAX of the
"True" lines.

i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and
MATCH... it is frustrating me now!!!

Many thanks...
 
T

T. Valko

either "True" (for sell orders) or "False" (for buy orders)

Are these TEXT values or Boolean values? Booleans appear in all upper case
and are centered in the cells: TRUE, FALSE.

Try one of these array formula** :

For TEXT values:

=MIN(IF(B1:B20="FALSE",A1:A20))
=MAX(IF(B1:B20="TRUE",A1:A20))

For Boolean logicals:

=MIN(IF(B1:B20=FALSE,A1:A20))
=MAX(IF(B1:B20=TRUE,A1:A20))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
A

AndrewK

Hell Valko,

Appreciate the response... the fields are boolean...

i have tried the formula and it works fine when there are no TRUE values in
the same IF lookup - once i strech this to include the TRUE it comes back
with 0 - this is the problem i have been facing...

Also i would like to include a formula using whole ranges such as B:B
instead of numbering rows (H2:H10) etc...

This sheet uses a self updating TXT import hourly... hence being unable to
keep chaging formulas hourly...

Thank you.
 
J

Jacob Skaria

For Boolean logicals:
=MIN(IF(B1:B20=FALSE,A1:A20))
=MAX(IF(B1:B20=TRUE,A1:A20))
Click to expand...

Try the above formulas itself but as normal (not array entered)

If this post helps click Yes
 
T

T. Valko

I don't quite understand this:
it works fine when there are no TRUE values
in the same IF lookup - once i strech this to
include the TRUE it comes back with 0 - this
is the problem i have been facing...
Click to expand...

You say if there are no TRUEs it works fine...

If there are no TRUEs in the range then the formula will return 0.

But then you say: include the TRUE it comes back with 0 = problem.

Are there any empty cells with a corresponding TRUE?
i would like to include a formula using whole
ranges such as B:B
Click to expand...

What version of Excel are you using? If you're using Excel 2007 then replace
the specific ranges with the entire column like B:B. If you're using any
other version then you can't use entire columns as range references. You can
use the entire column minus 1 row:

B1:B65535
B2:B65536
 
J

Jacob Skaria

I mean you are trying the same formulas itself but as normal (non array
entered). It should be array-entered as mentioned by Biff..



If this post helps click Yes
 
T

T. Valko

Try the above formulas itself but as normal (not array entered)

Why would you do that?

=MIN(IF(B1:B20=FALSE,A1:A20))
=MAX(IF(B1:B20=TRUE,A1:A20))

Normally enter one of those formulas in cell C35 and let me know what
happens.
 
A

AndrewK

Sorry to confuse - this is the table below;

price typeID bid
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
719000 649 FALSE
720000 649 FALSE
720000 649 FALSE
725000 649 FALSE
375000 649 TRUE
373000.05 649 TRUE
373000.04 649 TRUE
372608.4699 649 TRUE
371608.55 649 TRUE
371109.01 649 TRUE
371109 649 TRUE
371101 649 TRUE
371100 649 TRUE
363082.03 649 TRUE
360700.04 649 TRUE

The criteria is:

If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000

So i tried =MIN(IF(AND(B:B="649",C:C="FALSE")))

doesn't work right... although i don't know if the logic does either... =]
 
J

Jacob Skaria

Try the below formulas (again array entered)

649 is a number right ???

=MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20)))
=MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20)))

If this post helps click Yes
---------------
Jacob Skaria


AndrewK said:
Sorry to confuse - this is the table below;

price typeID bid
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
719000 649 FALSE
720000 649 FALSE
720000 649 FALSE
725000 649 FALSE
375000 649 TRUE
373000.05 649 TRUE
373000.04 649 TRUE
372608.4699 649 TRUE
371608.55 649 TRUE
371109.01 649 TRUE
371109 649 TRUE
371101 649 TRUE
371100 649 TRUE
363082.03 649 TRUE
360700.04 649 TRUE

The criteria is:

If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000

So i tried =MIN(IF(AND(B:B="649",C:C="FALSE")))

doesn't work right... although i don't know if the logic does either... =]

T. Valko said:
I don't quite understand this:


You say if there are no TRUEs it works fine...

If there are no TRUEs in the range then the formula will return 0.

But then you say: include the TRUE it comes back with 0 = problem.

Are there any empty cells with a corresponding TRUE?


What version of Excel are you using? If you're using Excel 2007 then replace
the specific ranges with the entire column like B:B. If you're using any
other version then you can't use entire columns as range references. You can
use the entire column minus 1 row:

B1:B65535
B2:B65536
Click to expand...
Click to expand...
 
A

AndrewK

sorry champ i get #N/A

and im sure the fields are a number - i have formatted them as a number...

Jacob Skaria said:
Try the below formulas (again array entered)

649 is a number right ???

=MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20)))
=MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20)))

If this post helps click Yes
---------------
Jacob Skaria


AndrewK said:
Sorry to confuse - this is the table below;

price typeID bid
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
719000 649 FALSE
720000 649 FALSE
720000 649 FALSE
725000 649 FALSE
375000 649 TRUE
373000.05 649 TRUE
373000.04 649 TRUE
372608.4699 649 TRUE
371608.55 649 TRUE
371109.01 649 TRUE
371109 649 TRUE
371101 649 TRUE
371100 649 TRUE
363082.03 649 TRUE
360700.04 649 TRUE

The criteria is:

If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000

So i tried =MIN(IF(AND(B:B="649",C:C="FALSE")))

doesn't work right... although i don't know if the logic does either... =]

T. Valko said:
I don't quite understand this:

it works fine when there are no TRUE values
in the same IF lookup - once i strech this to
include the TRUE it comes back with 0 - this
is the problem i have been facing...
=MAX(IF(B1:B20=TRUE,A1:A20))

You say if there are no TRUEs it works fine...

If there are no TRUEs in the range then the formula will return 0.

But then you say: include the TRUE it comes back with 0 = problem.

Are there any empty cells with a corresponding TRUE?

i would like to include a formula using whole
ranges such as B:B

What version of Excel are you using? If you're using Excel 2007 then replace
the specific ranges with the entire column like B:B. If you're using any
other version then you can't use entire columns as range references. You can
use the entire column minus 1 row:

B1:B65535
B2:B65536


--
Biff
Microsoft Excel MVP


Hell Valko,

Appreciate the response... the fields are boolean...

i have tried the formula and it works fine when there are no TRUE values
in
the same IF lookup - once i strech this to include the TRUE it comes back
with 0 - this is the problem i have been facing...

Also i would like to include a formula using whole ranges such as B:B
instead of numbering rows (H2:H10) etc...

This sheet uses a self updating TXT import hourly... hence being unable to
keep chaging formulas hourly...

Thank you.

:

either "True" (for sell orders) or "False" (for buy orders)

Are these TEXT values or Boolean values? Booleans appear in all upper
case
and are centered in the cells: TRUE, FALSE.

Try one of these array formula** :

For TEXT values:

=MIN(IF(B1:B20="FALSE",A1:A20))
=MAX(IF(B1:B20="TRUE",A1:A20))

For Boolean logicals:

=MIN(IF(B1:B20=FALSE,A1:A20))
=MAX(IF(B1:B20=TRUE,A1:A20))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


Hello there,

I have a problem...

I have a sheet of values and one certain column tells me if the line
item
is
either a but order or a sell order - nominated by either "True" (for
sell
orders) or "False" (for buy orders),

I would like to be able to return the most minimum value of all the
"False"
lines within the sheet also on a separate formula return the MAX of the
"True" lines.

i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX
and
MATCH... it is frustrating me now!!!

Many thanks...
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

Interesting...

--Did you try with the same formulas or on a different range.

--I tried exactly the same formulas with the below sample data in the range
A1:C20..In a new worksheet copy the below sample data...and try with the same
formulas....and feedback

In D2
=MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20)))
In D3
=MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20)))


Col A Col B Col C Col D
price typeID bid Results
700000 649 FALSE 700000
700000 649 FALSE 375000
700000 649 FALSE
700000 649 FALSE
719000 649 FALSE
720000 649 FALSE
720000 649 FALSE
725000 649 FALSE
375000 649 TRUE
373000.05 649 TRUE
373000.04 649 TRUE
372608.46 649 TRUE
371608.55 649 TRUE
371109.01 649 TRUE
371109 649 TRUE
371101 649 TRUE
371100 649 TRUE
363082.03 649 TRUE
360700.04 649 TRUE




If this post helps click Yes
---------------
Jacob Skaria


AndrewK said:
sorry champ i get #N/A

and im sure the fields are a number - i have formatted them as a number...

Jacob Skaria said:
Try the below formulas (again array entered)

649 is a number right ???

=MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20)))
=MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20)))

If this post helps click Yes
---------------
Jacob Skaria


AndrewK said:
Sorry to confuse - this is the table below;

price typeID bid
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
719000 649 FALSE
720000 649 FALSE
720000 649 FALSE
725000 649 FALSE
375000 649 TRUE
373000.05 649 TRUE
373000.04 649 TRUE
372608.4699 649 TRUE
371608.55 649 TRUE
371109.01 649 TRUE
371109 649 TRUE
371101 649 TRUE
371100 649 TRUE
363082.03 649 TRUE
360700.04 649 TRUE

The criteria is:

If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000

So i tried =MIN(IF(AND(B:B="649",C:C="FALSE")))

doesn't work right... although i don't know if the logic does either... =]

:

I don't quite understand this:

it works fine when there are no TRUE values
in the same IF lookup - once i strech this to
include the TRUE it comes back with 0 - this
is the problem i have been facing...
=MAX(IF(B1:B20=TRUE,A1:A20))

You say if there are no TRUEs it works fine...

If there are no TRUEs in the range then the formula will return 0.

But then you say: include the TRUE it comes back with 0 = problem.

Are there any empty cells with a corresponding TRUE?

i would like to include a formula using whole
ranges such as B:B

What version of Excel are you using? If you're using Excel 2007 then replace
the specific ranges with the entire column like B:B. If you're using any
other version then you can't use entire columns as range references. You can
use the entire column minus 1 row:

B1:B65535
B2:B65536


--
Biff
Microsoft Excel MVP


Hell Valko,

Appreciate the response... the fields are boolean...

i have tried the formula and it works fine when there are no TRUE values
in
the same IF lookup - once i strech this to include the TRUE it comes back
with 0 - this is the problem i have been facing...

Also i would like to include a formula using whole ranges such as B:B
instead of numbering rows (H2:H10) etc...

This sheet uses a self updating TXT import hourly... hence being unable to
keep chaging formulas hourly...

Thank you.

:

either "True" (for sell orders) or "False" (for buy orders)

Are these TEXT values or Boolean values? Booleans appear in all upper
case
and are centered in the cells: TRUE, FALSE.

Try one of these array formula** :

For TEXT values:

=MIN(IF(B1:B20="FALSE",A1:A20))
=MAX(IF(B1:B20="TRUE",A1:A20))

For Boolean logicals:

=MIN(IF(B1:B20=FALSE,A1:A20))
=MAX(IF(B1:B20=TRUE,A1:A20))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


Hello there,

I have a problem...

I have a sheet of values and one certain column tells me if the line
item
is
either a but order or a sell order - nominated by either "True" (for
sell
orders) or "False" (for buy orders),

I would like to be able to return the most minimum value of all the
"False"
lines within the sheet also on a separate formula return the MAX of the
"True" lines.

i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX
and
MATCH... it is frustrating me now!!!

Many thanks...
Click to expand...
Click to expand...
Click to expand...
 
A

AndrewK

Perhaps when im importing the data i am not doing it correctly??

im using 2007 auto importing... all data imported from txt file as "general"

Jacob Skaria said:
Interesting...

--Did you try with the same formulas or on a different range.

--I tried exactly the same formulas with the below sample data in the range
A1:C20..In a new worksheet copy the below sample data...and try with the same
formulas....and feedback

In D2
=MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20)))
In D3
=MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20)))


Col A Col B Col C Col D
price typeID bid Results
700000 649 FALSE 700000
700000 649 FALSE 375000
700000 649 FALSE
700000 649 FALSE
719000 649 FALSE
720000 649 FALSE
720000 649 FALSE
725000 649 FALSE
375000 649 TRUE
373000.05 649 TRUE
373000.04 649 TRUE
372608.46 649 TRUE
371608.55 649 TRUE
371109.01 649 TRUE
371109 649 TRUE
371101 649 TRUE
371100 649 TRUE
363082.03 649 TRUE
360700.04 649 TRUE




If this post helps click Yes
---------------
Jacob Skaria


AndrewK said:
sorry champ i get #N/A

and im sure the fields are a number - i have formatted them as a number...

Jacob Skaria said:
Try the below formulas (again array entered)

649 is a number right ???

=MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20)))
=MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20)))

If this post helps click Yes
---------------
Jacob Skaria


:

Sorry to confuse - this is the table below;

price typeID bid
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
700000 649 FALSE
719000 649 FALSE
720000 649 FALSE
720000 649 FALSE
725000 649 FALSE
375000 649 TRUE
373000.05 649 TRUE
373000.04 649 TRUE
372608.4699 649 TRUE
371608.55 649 TRUE
371109.01 649 TRUE
371109 649 TRUE
371101 649 TRUE
371100 649 TRUE
363082.03 649 TRUE
360700.04 649 TRUE

The criteria is:

If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000

So i tried =MIN(IF(AND(B:B="649",C:C="FALSE")))

doesn't work right... although i don't know if the logic does either... =]

:

I don't quite understand this:

it works fine when there are no TRUE values
in the same IF lookup - once i strech this to
include the TRUE it comes back with 0 - this
is the problem i have been facing...
=MAX(IF(B1:B20=TRUE,A1:A20))

You say if there are no TRUEs it works fine...

If there are no TRUEs in the range then the formula will return 0.

But then you say: include the TRUE it comes back with 0 = problem.

Are there any empty cells with a corresponding TRUE?

i would like to include a formula using whole
ranges such as B:B

What version of Excel are you using? If you're using Excel 2007 then replace
the specific ranges with the entire column like B:B. If you're using any
other version then you can't use entire columns as range references. You can
use the entire column minus 1 row:

B1:B65535
B2:B65536


--
Biff
Microsoft Excel MVP


Hell Valko,

Appreciate the response... the fields are boolean...

i have tried the formula and it works fine when there are no TRUE values
in
the same IF lookup - once i strech this to include the TRUE it comes back
with 0 - this is the problem i have been facing...

Also i would like to include a formula using whole ranges such as B:B
instead of numbering rows (H2:H10) etc...

This sheet uses a self updating TXT import hourly... hence being unable to
keep chaging formulas hourly...

Thank you.

:

either "True" (for sell orders) or "False" (for buy orders)

Are these TEXT values or Boolean values? Booleans appear in all upper
case
and are centered in the cells: TRUE, FALSE.

Try one of these array formula** :

For TEXT values:

=MIN(IF(B1:B20="FALSE",A1:A20))
=MAX(IF(B1:B20="TRUE",A1:A20))

For Boolean logicals:

=MIN(IF(B1:B20=FALSE,A1:A20))
=MAX(IF(B1:B20=TRUE,A1:A20))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


Hello there,

I have a problem...

I have a sheet of values and one certain column tells me if the line
item
is
either a but order or a sell order - nominated by either "True" (for
sell
orders) or "False" (for buy orders),

I would like to be able to return the most minimum value of all the
"False"
lines within the sheet also on a separate formula return the MAX of the
"True" lines.

i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX
and
MATCH... it is frustrating me now!!!

Many thanks...
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

HLOOKUP, LOOKUP & MATCH doesn't work. Seems not right. 1
Need help finding a substring in a array of strings. 1
IF Statement 2
Displaying multiple non-adjacent cells with 1 criteria 3
Finding dynamic maxima 9
Return corresponding information with minimum value 2
How to summarize copied data 2
Changing cell content on n auto-filtered column 4

Top