Conditional Max
 

If I have a 2 columns of data like this:

Col A Col B
a 1
b 2
c 8
a 5
b 6
c 1

What is the best way to retrieve the maximum entry in Col B for a
specific entry in Col A.


--
blatham
------------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
View this thread: http://www.excelforum.com/showthread...hreadid=539239

Conditional Max
 

Try this array formula*:

For values in A1:B10

C1: =MAX(IF(A1:A10="b",B1:B10))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=539239

Conditional Max
 

=SUBTOTAL(104,B:B)

SUBTOTAL can return any of the following:

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Where the first number includes hidden values and the second ignores them.

By using AutoFIlter on your columns, you can display only the a values and
the formula will only consider them.
--
Gary's Student


"blatham" wrote:


If I have a 2 columns of data like this:

Col A Col B
a 1
b 2
c 8
a 5
b 6
c 1

What is the best way to retrieve the maximum entry in Col B for a
specific entry in Col A.


--
blatham
------------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
View this thread: http://www.excelforum.com/showthread...hreadid=539239

Conditional Max
 

That's great, the array formula is most suitable for my need. Many
thanks


--
blatham
------------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
View this thread: http://www.excelforum.com/showthread...hreadid=539239