Help needed: Minimum value based upon either/or criteria

[Mfreit]

Hi all,

I'm trying to find a minimum value in one column (A) for all records i
which columns B or C are equal to a cell reference in a separat
worksheet. I've tried something like: =MIN(IF(OR(COLUMN B option, Colum
C option),Column A range)). But it's only giving me zeros which I kno
are not correct.

Any ideas?

Thanks in advance.

 

[plinius]

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.

 

[joeu2004]

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.

 

[Mfreit]

joeu2004,

Thank you so much, that worked like a charm. Can't tell you ho
frustrating that formula had been for me. And it's good to know abou
the OR function not working in functions of that type.

Cheers,

M

;1606924']"Mfreit"

There are two potential problems:

1. Formulas of that form must be array-entered, which means you mus
press
ctrl+shift+Enter instead of just Enter.

2. OR() does not work as intended in formulas of that form. You want t

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 expressio
is
zero only if both comparisons are FALSE row by row