Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help needed: Minimum value based upon either/or criteria
Hi all,
I'm trying to find a minimum value in one column (A) for all records in which columns B or C are equal to a cell reference in a separate worksheet. I've tried something like: =MIN(IF(OR(COLUMN B option, Column C option),Column A range)). But it's only giving me zeros which I know are not correct. Any ideas? Thanks in advance. M |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed: Minimum value based upon either/or criteria
Il 31/10/2012 07:03, Mfreit ha scritto:
Hi all, I'm trying to find a minimum value in one column (A) for all records in which columns B or C are equal to a cell reference in a separate worksheet. I've tried something like: =MIN(IF(OR(COLUMN B option, Column C option),Column A range)). But it's only giving me zeros which I know are not correct. Any ideas? Thanks in advance. M You must only confirm that formula pressing CONTROL+SHIFT+ENTER. E. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed: Minimum value based upon either/or criteria
"Mfreit" wrote:
I'm trying to find a minimum value in one column (A) for all records in which columns B or C are equal to a cell reference in a separate worksheet. I've tried something like: =MIN(IF(OR(COLUMN B option, Column C option),Column A range)). But it's only giving me zeros which I know are not correct. There are two potential problems: 1. Formulas of that form must be array-entered, which means you must press ctrl+shift+Enter instead of just Enter. 2. OR() does not work as intended in formulas of that form. You want to process row by row, but OR() will process the entire array argument, even if the formula is array-entered. An array-entered formula of the following form should do what you want (press ctrl+shift+Enter instead of just Enter): =MIN(IF((B1:B1000=Sheet2!X1)+(C1:C1000=Sheet2!X2) 0,A1:A1000)) Each comparison returns TRUE or FALSE. The arithmetic operation (plus) converts TRUE and FALSE into 1 and 0. The above conditional expression is zero only if both comparisons are FALSE row by row. |
#4
|
|||
|
|||
joeu2004,
Thank you so much, that worked like a charm. Can't tell you how frustrating that formula had been for me. And it's good to know about the OR function not working in functions of that type. Cheers, M Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to reference a cell based on the minimum of division of two cells, please help? | Excel Worksheet Functions | |||
Minimum With Two Criteria | Excel Discussion (Misc queries) | |||
Returning contents of a cell based upon the minimum value in a ran | Excel Worksheet Functions | |||
find minimum of range based on multiple criteria | Excel Worksheet Functions | |||
Minimum and Maximum with Complex Criteria | Excel Discussion (Misc queries) |