Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"MAXIF" workaround
Hi,
I want to create a formula that would act as a "MAXIF" 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"MAXIF" workaround
=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) "Rayo K" wrote in message ... Hi, I want to create a formula that would act as a "MAXIF" 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"MAXIF" workaround
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: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=513421 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Workaround for Cell Display Limitation in Excel 2000 | Excel Discussion (Misc queries) | |||
bug - type 1-4531 in any cell - returned Jan-51 workaround is '1-. | Excel Discussion (Misc queries) | |||
csv load 64K workaround | Excel Worksheet Functions | |||
Workaround for returning month name? | Excel Worksheet Functions | |||
Workaround for LOWER(A1:A100) | Excel Worksheet Functions |