Looking for a function...MIN IF ?

E

Emeric

Hello,

Here is my problem :
On my sheet, I have to 2 columns of n lines.
The first column contains n numbers. The second one contains n boolean
values ('true' or 'false').
I just want to find the minimum of the values of the first column for which
the corresponding value (in the second column) is 'true'.

Is there any conditional function like 'SUMIF()' that does that search or
any other way to do that ?

I have Excel 2002.
Thanks for any help !
 
B

Bob Phillips

=MIN(IF(K2:K20,J2:J20))

where K is the column of TRUEs and J is the numbers. It is an array formula,
so commit with Ctrol-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

Not yet...

=if(countif(b1:b100,True)=0,"No trues",max(if(b1:b100=true,a1:a100)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.
 
S

Sandy Mann

There may be better ways but with numbers in in column G and TRUE/FALSE in
H try:

=MIN(IF((H1:H10=TRUE)*(G1:G10)>0,G1:G10))

entered as an array formula with Ctrl + Shift + Enter

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
E

Emeric

thank you for you 3.
But.... is there any other way do have the same result automatically (I
mean, without being obliged to hit ctrl-shift-enter ? my sheet has a large
number of such operations !!)
Thanks again !
 
S

Sandy Mann

Bob Phillips said:
=MIN(IF(K2:K20,J2:J20))
Click to expand...

Yes Bob! - that's the result of understanding how IF functions work

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Bob Phillips said:
=MIN(IF(K2:K20,J2:J20))

where K is the column of TRUEs and J is the numbers. It is an array
formula,
so commit with Ctrol-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


message
Click to expand...
 
D

Dave Peterson

Nope.

In fact, I was thinking that the next version of excel would have =maxif(), but
my memory was wrong.


thank you for you 3.
But.... is there any other way do have the same result automatically (I
mean, without being obliged to hit ctrl-shift-enter ? my sheet has a large
number of such operations !!)
Thanks again !
Click to expand...
 
Top