Friday, January 30, 2015

MinIf and MaxIf...Array Formulas and UDFs

Situation

Yesterday I was creating a report from list of work orders.  Each time the consultant starts a new project a new work order is created.  The report needed two pieces of information

  • When each consultant first started at the client
  • The consultant's last work day, whether in the past or future

Found Solution

I tried to write the array formula, but that is not my strength, so I search the Internet for "MinIf", figuring that's what any Excel expert would call such a function. 

Sure enough, I found a great article, Finding MIN IF or MAX IF in Excel, that explained the array formulas.  

Easy cheesy!...Oops!....Aha!

As always, I did my best to follow direction.  As usual, Debra (the author), reminded the users to use CTL-SHIFT-ENTER because it was an array formula.

Hmmmm...instead of the date I got a zero!

Looking at my formula versus the example I noticed that I had used whole columns instead of limiting to the rows with data
  • Example: {=MIN(IF($C$2:$C$8=$C$11,$D$2:$D$8))}
  • Mine: {=MIN(IF('Source'!$A:$A=$A2,'Source'!$D:$D))}
Once I changed the formula to "{=MIN(IF('Source'!$A2:$A264=$A2,'Source'!$D2:$D264))}" it worked fine.

But I like the way I do things!

My way of looking at data tables and formulas is 
  • Data usually comes in columns of indeterminate length
  • It's quicker to click-choose (or event type) the reference for a column than figure out how many rows to include.
  • When adding data to the list, the selected range in the formula may not be updated.  

My Solution...MinIf and MaxIf UDFs

Being old and stuck in my ways, I decided to write the UDFs for these functions.

Function MaxIf(SearchRange As Range, Criteria, ResultRange As Range)
    Dim CurrMax
    Dim FirstFound As Boolean
    Dim i As Double
    Dim rngLimitedSearch As Range
    Dim rngLimitedResults As Range
    
    If SearchRange.Columns.Count = ResultRange.Columns.Count _
            And SearchRange.Rows.Count = ResultRange.Rows.Count _
            And (SearchRange.Columns.Count = 1 Or SearchRange.Rows.Count = 1) Then
        Set rngLimitedSearch = Intersect(SearchRange, SearchRange.Parent.UsedRange)
        Set rngLimitedResults = Intersect(ResultRange, ResultRange.Parent.UsedRange)
        For i = 1 To rngLimitedSearch.Cells.Count
            If rngLimitedSearch.Cells(i).Value = Criteria Then
                If Len(rngLimitedResults.Cells(i).Value) > 0 Then
                    If Not FirstFound Then
                        FirstFound = True
                        CurrMax = rngLimitedResults.Cells(i).Value
                    Else
                        If CurrMax < rngLimitedResults.Cells(i).Value Then
                            CurrMax = rngLimitedResults.Cells(i).Value
                        End If
                    End If
                End If
            End If
        Next i
    Else
        MaxIf = "#Range Err"
    End If
    If Not FirstFound Then
        MaxIf = "#N/A"
    Else
        MaxIf = CurrMax
    End If
End Function

Function MinIf(SearchRange As Range, Criteria, ResultRange As Range)
    Dim CurrMin
    Dim FirstFound As Boolean
    Dim i As Double
    Dim rngLimitedSearch As Range
    Dim rngLimitedResults As Range
    
    If SearchRange.Columns.Count = ResultRange.Columns.Count _
            And SearchRange.Rows.Count = ResultRange.Rows.Count _
            And (SearchRange.Columns.Count = 1 Or SearchRange.Rows.Count = 1) Then
        Set rngLimitedSearch = Intersect(SearchRange, SearchRange.Parent.UsedRange)
        Set rngLimitedResults = Intersect(ResultRange, ResultRange.Parent.UsedRange)
        For i = 1 To rngLimitedSearch.Cells.Count
            If rngLimitedSearch.Cells(i).Value = Criteria Then
                If Len(rngLimitedResults.Cells(i).Value) > 0 Then
                    If Not FirstFound Then
                        FirstFound = True
                        CurrMin = rngLimitedResults.Cells(i).Value
                    Else
                        If CurrMin > rngLimitedResults.Cells(i).Value Then
                            CurrMin = rngLimitedResults.Cells(i).Value
                        End If
                    End If
                End If
            End If
        Next i
    Else
        MinIf = "#Range Err"
    End If
    If Not FirstFound Then
        MinIf = "#N/A"
    Else
        MinIf = CurrMin
    End If
End Function