Hi all,
I have a user-defined function that translates Year/Quarter inputs into numbers. See below:
Function Quar2Num(yearquarter As Range)
Dim Result() As Variant
ReDim Result(1 To Application.WorksheetFunction.CountA(yearquarter))
On Error GoTo errhandle
For I = 1 To Application.WorksheetFunction.CountA(yearquarter)
Result(I) = Int(Left(yearquarter(I), 4)) + (CDbl(Right(yearquarter(I), 1)) - 1) / 4
Next
Quar2Num = Result
Exit Function
errhandle:
If (Err.Number = 13) Then
Result(I) = NaN
Resume Next
End If
End Function
I want to take the output from this user defined function, and feed it into an array formula in one of my spreadsheets, like this:
{=MIN(IF(A:A="1998-001-HUMB",quar2num(AM:AM),10))} to find the earliest listed year/quarter reporting date for a given project.
Unfortunately, the user-defined function is not playing well with the array formula. Instead of doing like an array formula normally would by matching the first criteria with the corresponding TRUE/FALSE outputs and then taking the MIN of those corresponding outputs, instead it is decoupling the logical test from the outputs. In other words, if there are three "TRUE"s, it is running my user-defined function three different times, each time for the whole column, rather than just running my function for the three individual values corresponding to each TRUE value.
Can anyone help with this??
Thanks,
Christine
Bookmarks