Conditional Minimums

[squidnum1]

Hey everyone,

I am trying to set up a conditional Minimum formula. On Sheet 1, I have 1000 IDs split into 20 different groups all on one rolling list. So Column A has all of the IDs, Coulmn B has the group name they are in, and Column C has the values of each. On another sheet (Sheet 2) I have the list of the 20 groups in Column A. I want to know if there is a formula that can give me the minimum for each group while also excluding 0's.

Any help would be great! Thanks!

 

[GDH]

Are you using Exce 2010 or 2007?

 

[squidnum1]

2007

 

[GDH]

What you refer to in Sheet 2 I have done in the same sheet so where applicable, add 'Sheet1!' before the cell references.

Column C has the values,
Column B has the group number.
Column E has the unique list of Group numbers (your Sheet 2 column A)

In 2007, try this

=IF(COUNTIFS($C$2:$C$1001,0,$B$2:$B$1001,E2)>0,SMALL(IF($B$2:$B$1001=E2,$C$2:$C$1001,""),(COUNTIFS($C$2:$C$1001,0,$B$2:$B$1001,E2)>0)+1),SMALL(IF($B$2:$B$1001=E2,$C$2:$C$1001,""),1))

 

[mikerickson]

Try the CSE formula
=MIN(IF(AND(Sheet1!$B$1:$B$1000="GroupOfInterest",Sheet1!$C$1:$C$1000<>0), Sheet1!$C$1:$C$1000))

This has to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[squidnum1]

Thanks guya! I will try both!