I have two VBA Excel functions: IdentifyOutliers and OutlierString. OutlierString calls IdentifyOutliers. IdentifyOutliers finds outliers in a named Excel range (Data_Table_1) in the same workbook. OutlierString then returns a sentence naming the outliers, such as "The outliers are 45, 65." When I place =OutlierString(Data_Table_1) in a cell and calculate sheet I get value error.
I know IdentifyOutliers works because use alone it returns outliers but spills them into adjacent cells which is undesirable. When I use them together I get #Value error. The sheets are set on manual calculate.
These are my functions:
Function IdentifyOutliers(rng As Range) As Variant
Dim mean As Double
Dim Q1 As Double
Dim Q2 As Double
Dim Q3 As Double
Dim IQR As Double
Dim n As Long
Dim i As Long
Dim cell As Range
Dim z As Double
Dim outliers() As Variant
Dim outlierCount As Long
n = rng.count
Q1 = Application.WorksheetFunction.Quartile_Inc(rng, 1)
Q2 = Application.WorksheetFunction.Quartile_Inc(rng, 2)
Q3 = Application.WorksheetFunction.Quartile_Inc(rng, 3)
IQR = Q3 - Q1
outlierCount = 0
ReDim outliers(1 To n)
For Each cell In rng
If cell.Value > Q3 + 1.5 * IQR Or cell.Value < Q1 - 1.5 * IQR Then
outlierCount = outlierCount + 1
outliers(outlierCount) = cell.Value
End If
Next cell
If outlierCount = 0 Then
IdentifyOutliers = "No outliers found."
Else
ReDim Preserve outliers(1 To outlierCount)
IdentifyOutliers = outliers
End If
End Function
and the 2nd function:
Function OutlierString(dataRange As Range) As String
Dim outliers() As Variant
Dim i As Long
Dim result As String
Dim count As Long
outliers = IdentifyOutliers(dataRange)
count = UBound(outliers) - LBound(outliers) + 1
If count > 0 Then
result = "The outliers are "
For i = LBound(outliers) To UBound(outliers)
result = result & outliers(i)
If i < UBound(outliers) Then
result = result & ", "
End If
Next i
Else
result = "No outliers found."
End If
OutlierString = result
End Function
The named range is only A2:D7. Does not include label in 1st row.
This the sheet with actual error:
Any help is appreciated.
The correct code for OutlierString is below:
Function OutlierString(dataRange As Range) As String
Dim outliers As Variant ' the first correction
Dim i As Long
Dim result As String
Dim count As Long
outliers = IdentifyOutliers(dataRange)
If IsArray(outliers) Then ' the second correction
count = UBound(outliers) - LBound(outliers) + 1
If count > 0 Then
result = "The outliers are "
For i = LBound(outliers) To UBound(outliers)
result = result & outliers(i)
If i < UBound(outliers) Then
result = result & ", "
End If
Next i
Else
result = "No outliers found."
End If
Else
result = outliers
End If
OutlierString = result
End Function
Key points are commented in the code.