 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006MIN and MAX Functions
How do I find the MIN and MAX of data based on 3 different conditions?  SignatureThanks, Leslie What conditions are you thinking of? I would say that there is only one; being the largest or being the smallest SignatureKind regards, Niek Otten Microsoft MVP - Excel | How do I find the MIN and MAX of data based on 3 different conditions? 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. SignatureThanks, Leslie > What conditions are you thinking of? I would say that there is only one; being the largest or being the smallest > > | How do I find the MIN and MAX of data based on 3 different conditions? =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) > 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 =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) > 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. 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" <bob.NGs@xxxx.om> wrote in message news:OTZFONrAHHA.4024@TK2MSFTNGP04.phx.gbl... =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) > How do I find the MIN and MAX of data based on 3 different conditions? > -- > Thanks, > Leslie One alternative is to do Data>AutoFilter and sort. But I would like to know the formula. Thanks. No idea how to write the MIN formula. Epinn "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eTrjgtwAHHA.4060@TK2MSFTNGP03.phx.gbl... 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" <bob.NGs@xxxx.om> wrote in message news:OTZFONrAHHA.4024@TK2MSFTNGP04.phx.gbl... =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) > How do I find the MIN and MAX of data based on 3 different conditions? > -- > Thanks, > Leslie 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 "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eTrjgtwAHHA.4060@TK2MSFTNGP03.phx.gbl... 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 =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) > How do I find the MIN and MAX of data based on 3 different conditions? > -- > Thanks, > Leslie > 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). 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 > 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:D10))) 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. 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" <bob.NGs@xxxx.om> wrote in message news:Oej9J52AHHA.4292@TK2MSFTNGP02.phx.gbl... "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eTrjgtwAHHA.4060@TK2MSFTNGP03.phx.gbl... > 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) 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:D10))) 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" <someone@example.com.NO_SPAM> wrote in message news:eohx7o4AHHA.4292@TK2MSFTNGP02.phx.gbl... 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 "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eTrjgtwAHHA.4060@TK2MSFTNGP03.phx.gbl... > 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:D10))) 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: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 "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eTrjgtwAHHA.4060@TK2MSFTNGP03.phx.gbl... > 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 The double unary is not necessary as well as the * operator. =MIN(IF((A1:A10="y")*(B1:B10=1)*(C1:C10="a"),D1:D10))) 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: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" <bob.NGs@xxxx.om> wrote in message news:Oej9J52AHHA.4292@TK2MSFTNGP02.phx.gbl... > 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) >> ......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"),D1:D10))) 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) "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eohx7o4AHHA.4292@TK2MSFTNGP02.phx.gbl... 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 "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eTrjgtwAHHA.4060@TK2MSFTNGP03.phx.gbl... > 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). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.