Finding the minimum of a subset

[booms]

Hi, this seems simple but I just can’t get it to work.





I’ve got two columns of data, and what I’m after is the third column.




I need a formula I can roll down that will set out the minimum of each group in the third column – I suspect an array would work?

Group

<tbody>
[TD="width: 28%"] Value
[/TD]
[TD="width: 18%"] Min of Group
[/TD]

[TD="width: 53%"] A
[/TD]
[TD="width: 28%"] -2
[/TD]
[TD="width: 18%"] -2
[/TD]

[TD="width: 53%"] A
[/TD]
[TD="width: 28%"] 4
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] A
[/TD]
[TD="width: 28%"] 0
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] B
[/TD]
[TD="width: 28%"] 3
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] B
[/TD]
[TD="width: 28%"] 6
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] B
[/TD]
[TD="width: 28%"] 0
[/TD]
[TD="width: 18%"] 0
[/TD]

[TD="width: 53%"] C
[/TD]
[TD="width: 28%"] -2
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] C
[/TD]
[TD="width: 28%"] -8
[/TD]
[TD="width: 18%"] -8
[/TD]

[TD="width: 53%"] D
[/TD]
[TD="width: 28%"] 3
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] D
[/TD]
[TD="width: 28%"] 9
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] D
[/TD]
[TD="width: 28%"] 4
[/TD]
[TD="width: 18%"][/TD]

[TD="width: 53%"] D
[/TD]
[TD="width: 28%"] -4
[/TD]
[TD="width: 18%"] -4
[/TD]

</tbody>

 

[steve the fish]

Enter this CTRL-SHIFT-ENTER:

=IF(B2=MIN(IF($A$2:$A$13=A2,$B$2:$B$13)),MIN(IF($A$2:$A$13=A2,$B$2:$B$13)),"")

 

[booms]

Perfect - exactly what I was after. Thanks!

 

[steve the fish]

Actually could be simplified to:

=IF(B2=MIN(IF($A$2:$A$13=A2,$B$2:$B$13)),B2,"")

 

[booms]

Actually could be simplified to:

=IF(B2=MIN(IF($A$2:$A$13=A2,$B$2:$B$13)),B2,"")

Thanks again. Out of interest, I'm running this array over about 100,000 lines. It seems to run incredibly slowly (as in, would take half an hour to finish). Is that what you'd expect and is there any way to speed it up?

 

[steve the fish]

Yes its a array formula so will be very slow. Can you use a pivot table? It would find the minimum values in blink of an eye

 

[booms]

Yes its a array formula so will be very slow. Can you use a pivot table? It would find the minimum values in blink of an eye

Perfect, exactly what I was looking for.