How do I find the MIN and MAX of data based on 3 different conditions?
--
Thanks,
Leslie

What conditions are you thinking of? I would say that there is only one; being the largest or being the smallest

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Leslie" wrote in message ...
| How do I find the MIN and MAX of data based on 3 different conditions?
| --
| Thanks,
| Leslie

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Just substitute actual ranges for rng1, 2, 3 and 4 (they must be the same
size), and your test values (enclose in quotation marks if strings).

--

HTH

Bob Phillips

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

"Leslie" wrote in message
...
How do I find the MIN and MAX of data based on 3 different conditions?
--
Thanks,
Leslie

I hope I can explain what I am asking so that it makes sense...

I have a sheet that is used to record results from urine samples of
employees. The employees are divided up into work areas and some
participants belong to a target group. There are 6 rounds of tests.

Using that information I need to find the smallest and largest results from
employees from a certain work area, that are members of the target group and
from a certain round. So, for example, I need to find out the lowest result
from the target group of Department 1 during Round 1 of the tests. Those are
the 3 conditions that I need to use.

The information is all listed in the sheet across columns. So, for
instance, column a as the work area, column b has a "y" or a "n" indicating
if that employee is in the target group, and column c has the results of
round 1 tests.

I hope this makes sense and that there is a way to solve my problem.
--
Thanks,
Leslie


"Niek Otten" wrote:

What conditions are you thinking of? I would say that there is only one; being the largest or being the smallest

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Leslie" wrote in message ...
| How do I find the MIN and MAX of data based on 3 different conditions?
| --
| Thanks,
| Leslie

Bob,

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

(1) I took out the extra closing bracket.
(2) I took out IF. This is based on what I have learned from SUMPRODUCT and Boolean.
(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.

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

Appreciate guidance.

Epinn

"Bob Phillips" wrote in message ...
=MAX(IF(rng=cond1)*(rng2=cond2)*(rng3=cond3),rng4) )

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Just substitute actual ranges for rng1, 2, 3 and 4 (they must be the same
size), and your test values (enclose in quotation marks if strings).

--

HTH

Bob Phillips

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

"Leslie" wrote in message
...
How do I find the MIN and MAX of data based on 3 different conditions?
--
Thanks,
Leslie

No idea how to write the MIN formula.

Epinn

"Epinn" wrote in message ...
Bob,

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

(1) I took out the extra closing bracket.
(2) I took out IF. This is based on what I have learned from SUMPRODUCT and Boolean.
(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.

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

Appreciate guidance.

Epinn

"Bob Phillips" wrote in message ...
=MAX(IF(rng=cond1)*(rng2=cond2)*(rng3=cond3),rng4) )

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Just substitute actual ranges for rng1, 2, 3 and 4 (they must be the same
size), and your test values (enclose in quotation marks if strings).

--

HTH

Bob Phillips

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

"Leslie" wrote in message
...
How do I find the MIN and MAX of data based on 3 different conditions?
--
Thanks,
Leslie

One alternative is to do DataAutoFilter and sort.

But I would like to know the formula.

Thanks.

Epinn

"Epinn" wrote in message ...
No idea how to write the MIN formula.

Epinn

"Epinn" wrote in message ...
Bob,

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

(1) I took out the extra closing bracket.
(2) I took out IF. This is based on what I have learned from SUMPRODUCT and Boolean.
(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.

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

Appreciate guidance.

Epinn

"Bob Phillips" wrote in message ...
=MAX(IF(rng=cond1)*(rng2=cond2)*(rng3=cond3),rng4) )

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Just substitute actual ranges for rng1, 2, 3 and 4 (they must be the same
size), and your test values (enclose in quotation marks if strings).

--

HTH

Bob Phillips

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

"Leslie" wrote in message
...
How do I find the MIN and MAX of data based on 3 different conditions?
--
Thanks,
Leslie

"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).

With mine it is simple

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

This is the problem with yours, as the tests resolve to 1/0, and when
multipiled by the value range there are still zeroes of which the min of
that is always 0.

--

HTH

Bob Phillips

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

"Epinn" wrote in message
...
No idea how to write the MIN formula.

Epinn

"Epinn" wrote in message
...
Bob,

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

(1) I took out the extra closing bracket.
(2) I took out IF. This is based on what I have learned from SUMPRODUCT and
Boolean.
(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.

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

Appreciate guidance.

Epinn

"Bob Phillips" wrote in message
...
=MAX(IF(rng=cond1)*(rng2=cond2)*(rng3=cond3),rng4) )

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Just substitute actual ranges for rng1, 2, 3 and 4 (they must be the same
size), and your test values (enclose in quotation marks if strings).

--

HTH

Bob Phillips

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

"Leslie" wrote in message
...
How do I find the MIN and MAX of data based on 3 different conditions?
--
Thanks,
Leslie

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

CORRECTION

Sorry for the confusion. We don't need the double unary.

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

should be

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

Not sure what I was thinking. I may have had too much fun with double unary and get too attached. <G

Will you forgive me, Sir.

Epinn

"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).

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

I did wonder why you added those, redundant as you have now found.

--

HTH

Bob Phillips

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

"Epinn" wrote in message
...
CORRECTION

Sorry for the confusion. We don't need the double unary.

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

should be

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

Not sure what I was thinking. I may have had too much fun with double unary
and get too attached. <G

Will you forgive me, Sir.

Epinn

"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).

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

If you mean the double unary, it works fine. I usually test my formulae before I post. Although they may not be perfect, they should work. Like SUMPRODUCT, MIN/IF works with both double unary and * coexisting. Of course, I take out -- when I know that it is not necessary.

Epinn

"Bob Phillips" wrote in message ...
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).

I mean

=MIN((rng1=cond1)*(rng2=cond2),rng3))

can't be made to work, for the reasons I explained earlier, not your version
of MIN(IF(...

--

HTH

Bob Phillips

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

"Epinn" wrote in message
...
......but I don't think your way can be made to work <<

If you mean the double unary, it works fine. I usually test my formulae
before I post. Although they may not be perfect, they should work. Like
SUMPRODUCT, MIN/IF works with both double unary and * coexisting. Of
course, I take out -- when I know that it is not necessary.

Epinn

"Bob Phillips" wrote in message
...
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).