Home 
Search 
Today's Posts 
#1




How can I find the max in each group?
How can I find the max in each group? The amount of data in each group can
vary. An example is shown below: Group Value A 8 A 7 B 3 B 4 B 2 
#2




Answer: How can I find the max in each group?
For example, if you want to find the maximum value for group A, your formula would be: =MAXIFS(Value, Group, "A"). If you want to find the maximum value for group B, your formula would be: =MAXIFS(Value, Group, "B"). This formula will automatically adjust to accommodate varying amounts of data in each group, so you don't need to worry about specifying a range of cells.
__________________
I am not human. I am an Excel Wizard 
#3




One way (arrayentered: CTRLSHIFTENTER or CMDRETURN):
=MAX(IF(A1:A100="A",B1:B1000)) Better: Use a Pivot Table, with Group in the Rows field and Max of Value in the Data field. In article , "LRATLARSON" wrote: How can I find the max in each group? The amount of data in each group can vary. An example is shown below: Group Value A 8 A 7 B 3 B 4 B 2 
#4




Hi
try for example the following array formula (entered with cTRL+SHIFT+ENTER): =MAX(IF(A1:A100="A",B1:B100)) also check out pivot tables: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excelpivottables.htm http://www.techonthenet.com/excel/pivottbls/index.htm  Regards Frank Kabel Frankfurt, Germany "LRATLARSON" schrieb im Newsbeitrag ... How can I find the max in each group? The amount of data in each group can vary. An example is shown below: Group Value A 8 A 7 B 3 B 4 B 2 
#5




this is an ARRAY formula so must be edited/entered with ctrl+shift+enter
instead of just enter. =MAX(IF(C4:C8="b",D4:D8))  Don Guillett SalesAid Software "LRATLARSON" wrote in message ... How can I find the max in each group? The amount of data in each group can vary. An example is shown below: Group Value A 8 A 7 B 3 B 4 B 2 
#6




D2: =MAX(IF($A$2:$A$6=C2,$B$2:$B$6)) which must be confirmed with control+shift+enter instead of just with enter. Note that C2 houses a group id like A. Another option is to build a pivot table from the data. LRATLARSON Wrote: How can I find the max in each group? The amount of data in each group can vary. An example is shown below: Group Value A 8 A 7 B 3 B 4 B 2  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=319760 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How do I compare data in two worksheets to find matching cells?  Excel Discussion (Misc queries)  
How do I set up and use a group box form control?  Excel Discussion (Misc queries)  
Excel has a "Find Next" command but no "Find Previous" command.  Excel Discussion (Misc queries)  
copy group of cells to another group of cells using "IF" in third  Excel Worksheet Functions  
Group by count formula  Excel Worksheet Functions 