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
No comments:
Post a Comment