Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting multiple conditions to return a figure from multiple cells | Excel Discussion (Misc queries) | |||
Transposing Multiple Cell References to Multiple Values (NOT total | Excel Discussion (Misc queries) | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) |