Max with Multiple Ifs
Hello All,
I need a Max statement with multiple ifs. Take for instance the example below, I need only the Max IF A=1 and C = 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF (Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF (Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far. So basically, for all values of A which are 1, I need the Max of B, if C is also 1, then for all values of A which are 2, I need the Max of B if C is 1, etc. Thanks, Chris Example: A B C 1 5 1 1 3 1 1 2 0 2 3 1 2 6 0 3 2 1 3 5 0 3 3 1 |
Max with Multiple Ifs
So what I'm looking for here is for when A=1 the cell will equal 5,
when A=2 the cell should say 2, and when A=3, the cell should say 3. On Jul 21, 3:47*pm, ChrisWWiese wrote: *Hello All, I need a Max statement with multiple ifs. Take for instance the example below, I need only the Max IF A=1 and C = 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF (Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF (Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far. So basically, for all values of A which are 1, I need the Max of B, if C is also 1, then for all values of A which are 2, I need the Max of B if C is 1, etc. Thanks, Chris Example: A * * * * B * * * * C 1 * * * * 5 * * * * *1 1 * * * * 3 * * * * *1 1 * * * * 2 * * * * *0 2 * * * * 3 * * * * *1 2 * * * * 6 * * * * *0 3 * * * * 2 * * * * *1 3 * * * * 5 * * * * *0 3 * * * * 3 * * * * *1 |
Max with Multiple Ifs
On Tue, 21 Jul 2009 12:51:27 -0700 (PDT), ChrisWWiese
wrote: So what I'm looking for here is for when A=1 the cell will equal 5, when A=2 the cell should say 2, and when A=3, the cell should say 3. On Jul 21, 3:47*pm, ChrisWWiese wrote: *Hello All, I need a Max statement with multiple ifs. Take for instance the example below, I need only the Max IF A=1 and C = 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF (Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF (Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far. So basically, for all values of A which are 1, I need the Max of B, if C is also 1, then for all values of A which are 2, I need the Max of B if C is 1, etc. Thanks, Chris Example: A * * * * B * * * * C 1 * * * * 5 * * * * *1 1 * * * * 3 * * * * *1 1 * * * * 2 * * * * *0 2 * * * * 3 * * * * *1 2 * * * * 6 * * * * *0 3 * * * * 2 * * * * *1 3 * * * * 5 * * * * *0 3 * * * * 3 * * * * *1 Try the following formula: =MAX((B$1:B$100)*(A$1:A$100=x)*(C$1:C$100=1)) Note: This is an array formula that has to be cofirmed with CTRL+SHIFT+ENTER rather than just ENTER. Replace the 100 on three places to suit the number of data rows in column A,B, and C. Change the "x" to 1, 2, or 3 to get the different results you expect (or replace "x" with a cell reference, e.g. D1 where you put the number. Hope this helps / Lars-Åke |
Max with Multiple Ifs
On Jul 21, 3:59*pm, Lars-Åke Aspelin
wrote: On Tue, 21 Jul 2009 12:51:27 -0700 (PDT), ChrisWWiese wrote: So what I'm looking for here is for when A=1 the cell will equal 5, when A=2 the cell should say 2, and when A=3, the cell should say 3. On Jul 21, 3:47*pm, ChrisWWiese wrote: *Hello All, I need a Max statement with multiple ifs. Take for instance the example below, I need only the Max IF A=1 and C = 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF (Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF (Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far. So basically, for all values of A which are 1, I need the Max of B, if C is also 1, then for all values of A which are 2, I need the Max of B if C is 1, etc. Thanks, Chris Example: A * * * * B * * * * C 1 * * * * 5 * * * * *1 1 * * * * 3 * * * * *1 1 * * * * 2 * * * * *0 2 * * * * 3 * * * * *1 2 * * * * 6 * * * * *0 3 * * * * 2 * * * * *1 3 * * * * 5 * * * * *0 3 * * * * 3 * * * * *1 Try the following formula: =MAX((B$1:B$100)*(A$1:A$100=x)*(C$1:C$100=1)) Note: This is an array formula that has to be cofirmed with CTRL+SHIFT+ENTER rather than just ENTER. Replace the 100 on three places to suit the number of data rows in column A,B, and C. Change the "x" to 1, 2, or 3 to get the different results you expect (or replace "x" with a cell reference, e.g. D1 where you put the number. Hope this helps / Lars-Åke- Hide quoted text - - Show quoted text - That works! Thank you! |
All times are GMT +1. The time now is 07:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com