| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Min If
I am looking for a function that is a lot like SUMIF function but is Min if.
Returns the min in a range by criteria. Range, Criteria, and Min range Any help would be great. Thanks in advance! Aaron |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Min If
Aaron,
The following array formula will return the minimum value from C3:C13 where the corresponding entry in B3:B13 is equal to the value in cell C1. If the value in C1 is not found in B3:B13, the result is #N/A. =IF(COUNTIF(B3:B13,C1)=0,NA(),MIN(IF(B3:B13=C1,C3: C13,FALSE))) This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See http://www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array formulas. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Aaron" wrote in message ... I am looking for a function that is a lot like SUMIF function but is Min if. Returns the min in a range by criteria. Range, Criteria, and Min range Any help would be great. Thanks in advance! Aaron |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Min If
Perhaps something like:
If(your criteria, Min(A1:P100),"No") HTH Otto "Aaron" wrote in message ... I am looking for a function that is a lot like SUMIF function but is Min if. Returns the min in a range by criteria. Range, Criteria, and Min range Any help would be great. Thanks in advance! Aaron |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Min If
This doesn't work. The data in the critera range has many of the same and
different values that I need a min against. The only solution that I have been able to come up with is a pivot table on Min and then vlookup it back to the spreadsheet. That is why I am looking for something in VB. Example below example PN |QTY| Min 123 | 4 | 2 123 | 5 | 2 123 | 6 | 2 568 | 8 | 8 568 | 9 | 8 238 | 5 | 5 238 | 6 | 5 123 | 2 | 2 Thanks, Aaron "Chip Pearson" wrote: Aaron, The following array formula will return the minimum value from C3:C13 where the corresponding entry in B3:B13 is equal to the value in cell C1. If the value in C1 is not found in B3:B13, the result is #N/A. =IF(COUNTIF(B3:B13,C1)=0,NA(),MIN(IF(B3:B13=C1,C3: C13,FALSE))) This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See http://www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array formulas. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Aaron" wrote in message ... I am looking for a function that is a lot like SUMIF function but is Min if. Returns the min in a range by criteria. Range, Criteria, and Min range Any help would be great. Thanks in advance! Aaron |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Min If
This doesn't work.
Actually what I posted does in fact work. You didn't post adequate detail. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Aaron" wrote in message ... This doesn't work. The data in the critera range has many of the same and different values that I need a min against. The only solution that I have been able to come up with is a pivot table on Min and then vlookup it back to the spreadsheet. That is why I am looking for something in VB. Example below example PN |QTY| Min 123 | 4 | 2 123 | 5 | 2 123 | 6 | 2 568 | 8 | 8 568 | 9 | 8 238 | 5 | 5 238 | 6 | 5 123 | 2 | 2 Thanks, Aaron "Chip Pearson" wrote: Aaron, The following array formula will return the minimum value from C3:C13 where the corresponding entry in B3:B13 is equal to the value in cell C1. If the value in C1 is not found in B3:B13, the result is #N/A. =IF(COUNTIF(B3:B13,C1)=0,NA(),MIN(IF(B3:B13=C1,C3: C13,FALSE))) This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See http://www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array formulas. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Aaron" wrote in message ... I am looking for a function that is a lot like SUMIF function but is Min if. Returns the min in a range by criteria. Range, Criteria, and Min range Any help would be great. Thanks in advance! Aaron |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Min If
Hi Chip,
Sorry, I put more detail to better explain the details. I also found this (Below) but get a #value when it is ran. What am I doing wrong? Please help. Public Function MinIf(Ref_Range As Range, Criterion As Variant, Min_Range As Range) As Single Dim cell As Range Dim minVal As Single Dim iRow As Integer, jCol As Integer, iCount As Integer, jCount As Integer iCount = Ref_Range.Rows.Count jCount = Ref_Range.Columns.Count minVal = Application.WorksheetFunction.Max(Min_Range) For iRow = 1 To iCount For jCol = 1 To jCount If Ref_Range.Cells(iRow, jCol).Value = Criterion Then If Min_Range.Cells(iRow, jCol).Value < minVal Then minVal = Min_Range.Cells(iRow, jCol).Value End If End If Next Next MinIf = minVal End Function "Chip Pearson" wrote: This doesn't work. Actually what I posted does in fact work. You didn't post adequate detail. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Aaron" wrote in message ... This doesn't work. The data in the critera range has many of the same and different values that I need a min against. The only solution that I have been able to come up with is a pivot table on Min and then vlookup it back to the spreadsheet. That is why I am looking for something in VB. Example below example PN |QTY| Min 123 | 4 | 2 123 | 5 | 2 123 | 6 | 2 568 | 8 | 8 568 | 9 | 8 238 | 5 | 5 238 | 6 | 5 123 | 2 | 2 Thanks, Aaron "Chip Pearson" wrote: Aaron, The following array formula will return the minimum value from C3:C13 where the corresponding entry in B3:B13 is equal to the value in cell C1. If the value in C1 is not found in B3:B13, the result is #N/A. =IF(COUNTIF(B3:B13,C1)=0,NA(),MIN(IF(B3:B13=C1,C3: C13,FALSE))) This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See http://www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array formulas. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Aaron" wrote in message ... I am looking for a function that is a lot like SUMIF function but is Min if. Returns the min in a range by criteria. Range, Criteria, and Min range Any help would be great. Thanks in advance! Aaron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
