software

Hi,

I want to create a formula that would act as a quot;MAXIFquot; function. I have data
in rows and I want to find the max of field A for a given field B.

For example:
A B C
4356 4 John
2341 2 Bill
3425 4 Joe
8734 2 Sam

I also want to be able to call data from field C that's on the same row as
the maximum.

I want to show:
Max 2: 8734 Sam
Max 4: 4356 John

How can I do this? I've tried pivottables and can't seem to get the results
I want.=MAX(IF(B2:B5=2,A2:A5))

and

=MAX(IF(B2:B5=2,C2:C5))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Rayo Kquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I want to create a formula that would act as a quot;MAXIFquot; function. I have
data
gt; in rows and I want to find the max of field A for a given field B.
gt;
gt; For example:
gt; A B C
gt; 4356 4 John
gt; 2341 2 Bill
gt; 3425 4 Joe
gt; 8734 2 Sam
gt;
gt; I also want to be able to call data from field C that's on the same row as
gt; the maximum.
gt;
gt; I want to show:
gt; Max 2: 8734 Sam
gt; Max 4: 4356 John
gt;
gt; How can I do this? I've tried pivottables and can't seem to get the
results
gt; I want.
gt;

If you use this formula in E2

=MAX(IF(B2:B5=2,A2:A5))

confirmed with CTRL SHIFT ENTER

then to get the associated name use a VLOOKUP

=VLOOKUP(E2,A2:C5,3,0)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=513421