Looking for a function...MIN IF ?

[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 !

 

[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)

 

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

 

[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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[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 !

 

[Sandy Mann]

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

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

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

=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

 

[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 !