Here is my Office 365 LAMBDA Excel Formula that can be used to perform Outlier Correction on a range of values including the use of a fence coefficient (test function taking a range and a double value) Back · Purchase

=LAMBDA(ARRAY,FENCE,LET(
        QTL_1,QUARTILE.INC(ARRAY,1),
        QTL_3,QUARTILE.INC(ARRAY,3),
        IQR,QTL_3-QTL_1,
        LOWER,QTL_1-FENCE*(IQR),
        UPPER,QTL_3+FENCE*(IQR),
        RESULT,IF(ARRAY>UPPER,UPPER,IF(ARRAY<LOWER,LOWER,ARRAY)),RESULT))($J$16:$X$16,1.5)

Add a Named Range called OutlierCorrection.Quartile and then Copy & Paste the formula below:

=LAMBDA(ARRAY,FENCE,LET(
        QTL_1,QUARTILE.INC(ARRAY,1),
        QTL_3,QUARTILE.INC(ARRAY,3),
        IQR,QTL_3-QTL_1,
        LOWER,QTL_1-FENCE*(IQR),
        UPPER,QTL_3+FENCE*(IQR),
        RESULT,IF(ARRAY>UPPER,UPPER,IF(ARRAY<LOWER,LOWER,ARRAY)),RESULT))

Use the Function like this in any Worksheet in Excel for a range of any size ($J$16:$X$16 are the actuals, 1.5 is the fence):

=OutlierCorrection.Quartile($J$16:$X$16,1.5)

Chart demonstrating the LAMBDA Outlier Correction Function on a range of 15 values with a FENCE of 1.5

---

Here is my Office 365 LAMBDA Excel Formula that can be used to perform Outlier Correction on a range of values including the use of a fence coefficient (test function taking a range and a double value) as well as a PERIODS range and an EXCLUDE comma-delimited list of seasonal periods to exclude from the algorithm Back · Purchase

=LAMBDA(ARRAY,FENCE,PERIODS,EXCLUDE,LET(
        QTL_1,QUARTILE.INC(ARRAY,1),
        QTL_3,QUARTILE.INC(ARRAY,3),
        IQR,QTL_3-QTL_1,
        LOWER,QTL_1-FENCE*(IQR),
        UPPER,QTL_3+FENCE*(IQR),
        EXCLUDEPERIODS,IF(ISNUMBER(SEARCH(","&PERIODS&",",","&EXCLUDE&",")),PERIODS),
        RESULT,IF(ARRAY>UPPER,IF(PERIODS<>EXCLUDEPERIODS,UPPER,ARRAY),
        IF(ARRAY<LOWER,IF(PERIODS<>EXCLUDEPERIODS,LOWER,ARRAY),ARRAY)),RESULT))($D$28:$R$28,1.5,$D27:$R27,$L$22)

Add a Named Range called OutlierCorrection.Quartile.Exclude and then Copy & Paste the formula below:

=LAMBDA(ARRAY,FENCE,PERIODS,EXCLUDE,LET(
        QTL_1,QUARTILE.INC(ARRAY,1),
        QTL_3,QUARTILE.INC(ARRAY,3),
        IQR,QTL_3-QTL_1,
        LOWER,QTL_1-FENCE*(IQR),
        UPPER,QTL_3+FENCE*(IQR),
        EXCLUDEPERIODS,IF(ISNUMBER(SEARCH(","&PERIODS&",",","&EXCLUDE&",")),PERIODS),
        RESULT,IF(ARRAY>UPPER,IF(PERIODS<>EXCLUDEPERIODS,UPPER,ARRAY),
        IF(ARRAY<LOWER,IF(PERIODS<>EXCLUDEPERIODS,LOWER,ARRAY),ARRAY)),RESULT))

Use the Function like this in any Worksheet in Excel for a range of any size ($D$28:$R$28 are the actuals, 1.5 is the fence, $D27:$R27 are the periods, $L$22 is a comma-delimited list of seasonal periods to exclude):

=OutlierCorrection.Quartile.Exclude($D$28:$R$28,1.5,$D27:$R27,$L$22)

Chart demonstrating the LAMBDA Exclude Outlier Correction Function on a range of 15 values with a FENCE of 1.5, using 15 periods and a comma-delimited list of periods to exclude i.e. 4,12. Exclude periods can be WK49,WK50,WK51 etc. thereby preventing the cleaning of seasonal Weeks by the algorithm

Sometimes you may not want to adjust the LOWER FENCE, for example, consider Actuals that do not include Returns (negative values) then you can modify the LAMBDA to check if the values are Zero before applying the LOWER Fence - if they are zero then there may be no reason to raise the level. As a workaround you can adjust the LAMBDA accordingly

=LAMBDA(ARRAY,FENCE,PERIODS,EXCLUDE,LET(
        QTL_1,QUARTILE.INC(ARRAY,1),
        QTL_3,QUARTILE.INC(ARRAY,3),
        IQR,QTL_3-QTL_1,
        LOWER,QTL_1-FENCE*(IQR),
        UPPER,QTL_3+FENCE*(IQR),
        EXCLUDEPERIODS,IF(ISNUMBER(SEARCH(","&PERIODS&",",","&EXCLUDE&",")),PERIODS),
        RESULT,IF(ARRAY>UPPER,IF(PERIODS<>EXCLUDEPERIODS,UPPER,ARRAY),
        IF(AND(ARRAY<>0,ARRAY<LOWER),IF(PERIODS<>EXCLUDEPERIODS,LOWER,ARRAY),ARRAY)),RESULT))

There may also be cases whereby you output zeros that are then taken into the LAMBDA Function. To prevent zeros skewing any output of this Function, you can explicitly Filter out the zeros when selecting the Upper and Lower Quartile using QUARTILE.INC(). As a workaround you can adjust the LAMBDA accordingly

=LAMBDA(ARRAY,FENCE,PERIODS,EXCLUDE,LET(
        QTL_1,QUARTILE.INC(FILTER(ARRAY,ARRAY<>0,""),1),
        QTL_3,QUARTILE.INC(FILTER(ARRAY,ARRAY<>0,""),3),
        IQR,QTL_3-QTL_1,
        LOWER,QTL_1-FENCE*(IQR),
        UPPER,QTL_3+FENCE*(IQR),
        EXCLUDEPERIODS,IF(ISNUMBER(SEARCH(","&PERIODS&",",","&EXCLUDE&",")),PERIODS),
        RESULT,IF(ARRAY>UPPER,IF(PERIODS<>EXCLUDEPERIODS,UPPER,ARRAY),
        IF(AND(ARRAY<>0,ARRAY<LOWER),IF(PERIODS<>EXCLUDEPERIODS,LOWER,ARRAY),ARRAY)),RESULT))

---

Here is my VBA UDF for Excel that can be used to perform Outlier Correction on a range of values >0 with optional exclude time series periods and fence (very useful to prevent cleaning of seasonal periods). Copy & Paste this Code into any Standard Code Module Back · Purchase

Public Function QuartileOutlierCorrection(ByVal Known_ys As Range, ByVal Known_xs As Range, Optional ByVal Exclude_xs As String = vbNullString, Optional ByVal Fence As Double = 1) As Variant
    
    Dim X As Long
    Dim Y As Long
    Dim YS As Variant
    Dim XS As Variant
    
    YS = Known_ys
    XS = Known_xs
    For X = 1 To UBound(YS, 2)
        If YS(1, X) <> Empty Then YS(1, X) = Val(YS(1, X))
        If YS(1, X) <= 0 Then
            YS(1, X) = Empty
        End If
    Next X
    
    Dim Q1 As Double
    Dim Q3 As Double
    Dim IQR As Double
    Dim Lower As Double
    Dim Upper As Double
        
    On Error Resume Next
    Q1 = Application.WorksheetFunction.Quartile_Inc(YS, 1)
    Q3 = Application.WorksheetFunction.Quartile_Inc(YS, 3)
    IQR = Q3 - Q1
    Lower = Q1 - (Fence * IQR)
    Upper = Q3 + (Fence * IQR)
    On Error GoTo 0

    If Exclude_xs = vbNullString Then
        For X = 1 To UBound(YS, 2)
            If YS(1, X) <> Empty Then
                If YS(1, X) < Lower Then
                    YS(1, X) = Lower
                ElseIf YS(1, X) > Upper Then
                    YS(1, X) = Upper
                End If
            End If
        Next X
    Else
        On Error Resume Next
        Dim Match As Long
        For Y = 1 To UBound(XS, 2)
            Match = InStr(1, Exclude_xs, Left(XS(1, Y), 2), vbTextCompare)
            If Match = 0 Then
                X = Y
                If YS(1, X) <> Empty Then
                    If YS(1, X) < Lower Then
                        YS(1, X) = Lower
                    ElseIf YS(1, X) > Upper Then
                        YS(1, X) = Upper
                    End If
                End If
            End If
        Next Y
        On Error GoTo 0
    End If
    
    QuartileOutlierCorrection = YS
    
End Function

Function information is added to the Macro Table in the 'ThisWorkbook' Sheet giving you intellisense about the Named Arguments in the 'fx' (Formula Box) in Excel. Copy the Code below into the 'ThisWorkbook' Sheet Code Module:

Private Sub Workbook_Open()
 
    On Error Resume Next
    Dim FunctionName As String
    Dim FunctionDesc As String
    Dim ArgDesc(1 To 4) As String

    FunctionName = "QuartileOutlierCorrection"
    FunctionDesc = "Performs quartile outlier correction on a Range of values >=0 with optional exclude time series periods and fence"
    ArgDesc(1) = "Provide the Known_ys as the input values for the algorithm"
    ArgDesc(2) = "Provide the Known_xs as a time series of values for the algorithm"
    ArgDesc(3) = "Optional: A delimited list of Exclude_xs values in any order to exclude from the algorithm"
    ArgDesc(4) = "Optional: A Fence value to adjust the limits of the upper and lower quartiles"
    Application.MacroOptions Macro:=FunctionName, Description:=FunctionDesc, Category:=vbNullString, ArgumentDescriptions:=ArgDesc
    On Error GoTo 0

End Sub

Use the UDF like this in any Worksheet in Excel for a range of any size:

' standard UDF call (Known_ys := values, Known_xs := time periods)
=QuartileOutlierCorrection($B8:$BA8,$B$6:$BA$6)

' exclude time periods ($BB$2 is a single cell reference with a comma-delimited list i.e. '3, 21')
=QuartileOutlierCorrection($B8:$BA8,$B$6:$BA$6,$BB$2)

Chart demonstrating the VBA UDF Outlier Correction Function on a range of 15 values with a FENCE of 1.5 and excluding the seasonal point 12