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


"AndrewK" wrote in message
...
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.

"T. Valko" wrote:

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


"AndrewK" wrote in message
...
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...