The double unary is not necessary as well as the * operator.

=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D1:D1 0)))

As I said, MIN is easy using my technique, as I said earlier

=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng 4))

but I don't think your way can be made to work

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob, my dear teacher,

I am so happy to let you know that I have figured out how to write the MIN
formula *on my own*.

=MIN(IF(--(A1:A10="y")*(B1:B10=1)*--(C1:C10="a")=1,(D1:D10)))

The above was written *BEFORE* I read the solution in your post. After
reading your formula, I am going to drop =1.

Being able to work out MIN, I realized why I couldn't get your MAX formula
work previously. I missed the double unary. You are absolutely right, both
yours and mine work.

Please correct me if I am wrong. For MAX, we can use either your formula
(with IF) or mine (without IF). For MIN, we must use our mutual formula
(with IF). I did know why Boolean didn't work for MIN because I used
Evaluate Formula and found out that 0 translated from FALSE was picked up
every time. But, I didn't know how to fix it yesterday. I tried nested IF
but it didn't work. Today it dawns on me that I don't have to go with
straight IF or straight Boolean, I can have a combination. Hence, my above
solution. The IF formula can pick up the zero in the range to be MIN'd if
zero is the minimum value.

Bob, all the help/training on SUMPRODUCT, Boolean etc. that you and Roger
have provided for me has helped me to go beyond SUMPRODUCT. Case in point -
MIN, MAX. It takes time. The more I practise, the better. Thanks again,
Bob.

Leslie, thanks very much for a good question laid out in detail. Please
feel free to post if you have any follow-up questions.

Cheers,

Epinn

"Bob Phillips" wrote in message
...

"Epinn" wrote in message
...

I made some modification to the formula and I got the correct result.

(1) I took out the extra closing bracket.

Actually, I made a msitake and omitted an early opening bracket. It should
havfe been

=MAX(IF(rng=cond1)*(rng2=cond2)*(rng3=cond3),rng4) )

as an arary formula, will now give the correct result


(2) I took out IF. This is based on what I have learned from SUMPRODUCT
and Boolean.


Not necessary (when we have correct syntax <G), but an alternative approach
that also works.


(3) I had to replace the comma with *. I don't understand why.
For SUMPRODUCT either * or comma would have worked in a case like this.
But for MAX and SUM I have to use * instead of comma to get the correct
result.
This is my discovery for today.


Because it ain't SUMPRODUCT. SP allows ranges delimited by comma and does
the implict product. MAX also allows ranges to also be delimited by comma,
but there is no implicit product, so it takes the makes of the resultant
array of the tests, and the final range, NOT the final range where the
resultant array is TRUE.


=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1: D10)) committed with
CSE.


Will be interesting to time this and see if it is quicker (I expect it to be
so).