L
Leslie
How do I find the MIN and MAX of data based on 3 different conditions?
N
Niek Otten
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
| How do I find the MIN and MAX of data based on 3 different conditions?
| --
| Thanks,
| Leslie
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| How do I find the MIN and MAX of data based on 3 different conditions?
| --
| Thanks,
| Leslie
B
Bob Phillips
=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)
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)
L
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.
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.
E
Epinn
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
10)) committed with CSE.
Appreciate guidance.
Epinn
=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)
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
10)) committed with CSE.Appreciate guidance.
Epinn
=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)
E
Epinn
No idea how to write the MIN formula.
Epinn
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")*(D110)) committed with CSE.
Appreciate guidance.
Epinn
=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)
Epinn
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
10)) committed with CSE.Appreciate guidance.
Epinn
=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)
E
Epinn
One alternative is to do Data>AutoFilter and sort.
But I would like to know the formula.
Thanks.
Epinn
No idea how to write the MIN formula.
Epinn
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")*(D110)) committed with CSE.
Appreciate guidance.
Epinn
=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)
But I would like to know the formula.
Thanks.
Epinn
No idea how to write the MIN formula.
Epinn
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
10)) committed with CSE.Appreciate guidance.
Epinn
=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)
B
Bob Phillips
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
and Boolean.
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
CSE.=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1
Will be interesting to time this and see if it is quicker (I expect it to be
so).
B
Bob Phillips
With mine it is simple
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
No idea how to write the MIN formula.
Epinn
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")*(D110)) committed with CSE.
Appreciate guidance.
Epinn
=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)
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
No idea how to write the MIN formula.
Epinn
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
10)) committed with CSE.Appreciate guidance.
Epinn
=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)
E
Epinn
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,(D110)))
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
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
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
Will be interesting to time this and see if it is quicker (I expect it to be
so).
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
10))) 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
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
and Boolean.
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
CSE.=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1
Will be interesting to time this and see if it is quicker (I expect it to be
so).
E
Epinn
CORRECTION
Sorry for the confusion. We don't need the double unary.
=MIN(IF(--(A1:A10="y")*(B1:B10=1)*--(C1:C10="a")=1,(D110)))
should be
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),(D110)))
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
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,(D110)))
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
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
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
Will be interesting to time this and see if it is quicker (I expect it to be
so).
Sorry for the confusion. We don't need the double unary.
=MIN(IF(--(A1:A10="y")*(B1:B10=1)*--(C1:C10="a")=1,(D1
10))) should be
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),(D1
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
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
10))) 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
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
and Boolean.
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
CSE.=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1
Will be interesting to time this and see if it is quicker (I expect it to be
so).
B
Bob Phillips
The double unary is not necessary as well as the * operator.
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D110)))
As I said, MIN is easy using my technique, as I said earlier
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
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,(D110)))
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
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
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
Will be interesting to time this and see if it is quicker (I expect it to be
so).
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D1
10)))As I said, MIN is easy using my technique, as I said earlier
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
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
10)))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
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
and Boolean.
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
CSE.=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1
Will be interesting to time this and see if it is quicker (I expect it to be
so).
B
Bob Phillips
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)
CORRECTION
Sorry for the confusion. We don't need the double unary.
=MIN(IF(--(A1:A10="y")*(B1:B10=1)*--(C1:C10="a")=1,(D110)))
should be
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),(D110)))
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
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,(D110)))
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
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
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
Will be interesting to time this and see if it is quicker (I expect it to be
so).
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
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
10)))should be
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),(D1
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
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
10)))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
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
and Boolean.
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
CSE.=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1
Will be interesting to time this and see if it is quicker (I expect it to be
so).
E
Epinn
......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
The double unary is not necessary as well as the * operator.
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D110)))
As I said, MIN is easy using my technique, as I said earlier
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
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,(D110)))
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
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
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
Will be interesting to time this and see if it is quicker (I expect it to be
so).
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
The double unary is not necessary as well as the * operator.
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D1
10)))As I said, MIN is easy using my technique, as I said earlier
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
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
10)))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
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
and Boolean.
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
CSE.=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1
Will be interesting to time this and see if it is quicker (I expect it to be
so).
B
Bob Phillips
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)
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
The double unary is not necessary as well as the * operator.
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D110)))
As I said, MIN is easy using my technique, as I said earlier
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
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,(D110)))
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
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
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
Will be interesting to time this and see if it is quicker (I expect it to be
so).
=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)
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
The double unary is not necessary as well as the * operator.
=MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D1
10)))As I said, MIN is easy using my technique, as I said earlier
=MIN(IF((rng1=cond1)*(rng2=cond2)*(rng3=cond3),rng4))
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)
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
10)))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
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
and Boolean.
Not necessary (when we have correct syntax <G>), but an alternative approach
that also works.
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.
CSE.=MAX((A1:A10="y")*(B1:B10=1)*(C1:C10="DeptA")*(D1
Will be interesting to time this and see if it is quicker (I expect it to be
so).