On Fri, 8 Apr 2011 18:35:55 -0700 (PDT), Mike wrote:

Hi everyone,

Say I have this:

A B
1 3
1 4
1 5
1 6
2 1
2 2
2 3
2 4

While A=1, what is the MIN in B (answer=3)
While A=1, what is the MAX in B (answer=6)
While A=2, what is the MIN in B (answer=1)
While A=2, what is the MAX in B (answer=4)

Any excel formula to do this?

Thanks,
Mike


With E1 containing either a 1 or a 2:

These formulas must be **array-entered**:

=MAX(IF(A1:A8=E1,B1:B8))
=MIN(IF(A1:A8=E1,B1:B8))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.