FInding Min Value with Criteria
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...
|