Formula or UDF to calculate Semi Variance

M

Monroe

New Member
Joined
Dec 10, 2002
Messages
12
Is anyone aware of a fomula or know how to create a UDF to calculate semi-variance for a range of data? I would like to be able to calculate semi-variance using 2 different downside hurdles: (1) the data's mean, and (2) a fixed number such as zero.

Any help or ideas? Thanks!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
VERY LONG POST!

The following UDFs will give you a sizable number of options to calculate the semi-variance. Take the square root to get the semi-standard deviation.

Lots of options available, so please post back with questions. Also, watch the line wrap.

You want the SemiVariance_Absolute() function. The other is provided should you need that as well.

Code: 
Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'   Optional function argument definitions
'
'   CalcType (Byte)
'       0   arithmetic
'       1   geometric
'
'   Per_Yr (Double)
'       0   cumulative
'       #   annualized at # of periods per year (e.g. 12 = monthly)
'
'   InputType (Byte) and ReturnType (Byte)
'       0   values are in basis point format (e.g 7.5 = 7.5%)
'       1   values are in mathematical percentage format (e.g. .075 = 7.5%)
'
'   Pop_or_Sample (Byte)
'       0   population
'       1   sample
'
'   UpDown (Byte)
'       0   positive
'       1   negative
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Function _
SemiVariance_Relative _
    ( _
        Returns, _
        BenchmarkReturns, _
        Optional CalcType As Byte = 0, _
        Optional Per_Yr As Double = 0, _
        Optional InputType As Byte = 0, _
        Optional ReturnType As Byte = 0, _
        Optional Pop_or_Sample As Byte = 0, _
        Optional UpDown As Byte = 0 _
    ) As Double
    
    Dim arrReturns, arrBmarkReturns, dblResult As Double
        
    arrReturns = DataToArray_WealthRatio(Returns, InputType, False)
    arrBmarkReturns = DataToArray_WealthRatio(BenchmarkReturns, InputType, False)
    Per_Yr = Abs(Per_Yr)
    
    If Per_Yr = 0 Then  ''' cumulative
        If CalcType = 0 Then  ''' arithmetic
            If Pop_or_Sample = 0 Then ''' population
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeArithPopulation_Up(arrReturns, arrBmarkReturns)
                Else  ''' negative
                    dblResult = SemiVar_RelativeArithPopulation_Down(arrReturns, arrBmarkReturns)
                End If
            Else  ''' sample
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeArithSample_Up(arrReturns, arrBmarkReturns)
                Else  ''' negative
                    dblResult = SemiVar_RelativeArithSample_Down(arrReturns, arrBmarkReturns)
                End If
            End If
        Else   ''' geometric
            If Pop_or_Sample = 0 Then ''' population
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeGeomPopulation_Up(arrReturns, arrBmarkReturns)
                Else  ''' negative
                    dblResult = SemiVar_RelativeGeomPopulation_Down(arrReturns, arrBmarkReturns)
                End If
            Else  ''' sample
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeGeomSample_Up(arrReturns, arrBmarkReturns)
                Else  ''' negative
                    dblResult = SemiVar_RelativeGeomSample_Down(arrReturns, arrBmarkReturns)
                End If
            End If
        End If
    Else  ''' annualized
        If CalcType = 0 Then  ''' arithmetic
            If Pop_or_Sample = 0 Then ''' population
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeArithPopulation_Annual_Up(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                Else  ''' negative
                    dblResult = SemiVar_RelativeArithPopulation_Annual_Down(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                End If
            Else  ''' sample
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeArithSample_Annual_Up(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                Else  ''' negative
                    dblResult = SemiVar_RelativeArithSample_Annual_Down(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                End If
            End If
        Else   ''' geometric
            If Pop_or_Sample = 0 Then ''' population
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeGeomPopulation_Annual_Up(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                Else  ''' negative
                    dblResult = SemiVar_RelativeGeomPopulation_Annual_Down(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                End If
            Else  ''' sample
                If UpDown = 0 Then  ''' positive
                    dblResult = SemiVar_RelativeGeomSample_Annual_Up(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                Else  ''' negative
                    dblResult = SemiVar_RelativeGeomSample_Annual_Down(arrReturns, arrBmarkReturns, Abs(Per_Yr))
                End If
            End If
        End If
    End If
    SemiVariance_Relative = dblResult * IIf(ReturnType, 1, 100)
    Erase arrReturns
    Erase arrBmarkReturns
    End Function
Function _
SemiVariance_Absolute _
    ( _
        Returns, _
        ThresholdReturn As Double, _
        Optional CalcType As Byte = 0, _
        Optional Per_Yr As Double = 0, _
        Optional InputType As Byte = 0, _
        Optional ReturnType As Byte = 0, _
        Optional Pop_or_Sample As Byte = 0, _
        Optional UpDown As Byte = 0 _
    ) As Double
    
    
    '''''''''''''''''''''''''''''''''''''''''''''''
    '
    '   Use the mean of the data as the target threshold
    '   argument to find upside/downside deviations
    '   from the actual data (i.e. the "target" is
    '   the average of the historical data
    '
    '   Use AVERAGE() for arithmentic mean, GEOMEAN() for geometric
    '
    '''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim arrReturns, TargetVal As Double, dblResult As Double
        
    arrReturns = DataToArray(Returns, InputType, False)
    TargetVal = ThresholdReturn / IIf(InputType, 1, 100)
    Per_Yr = Abs(Per_Yr)
      
    If Per_Yr = 0 Then  ''' cumulative
        If CalcType = 0 Then  ''' arithmetic
            If Pop_or_Sample = 0 Then ''' population
                dblResult = SemiVar_AbsolutePopulation(DiffArr_ArithAbsolute(arrReturns, TargetVal, UpDown))
            Else  ''' sample
                dblResult = SemiVar_AbsoluteSample(DiffArr_ArithAbsolute(arrReturns, TargetVal, UpDown))
            End If
        Else   ''' geometric
            If Pop_or_Sample = 0 Then ''' population
                dblResult = SemiVar_AbsolutePopulation(DiffArr_GeomAbsolute(arrReturns, TargetVal, UpDown))
            Else  ''' sample
                dblResult = SemiVar_AbsoluteSample(DiffArr_GeomAbsolute(arrReturns, TargetVal, UpDown))
            End If
        End If
    Else  ''' annualized
        If CalcType = 0 Then  ''' arithmetic
            If Pop_or_Sample = 0 Then ''' population
                dblResult = SemiVar_AbsolutePopulationAnnual(DiffArr_ArithAbsolute(arrReturns, TargetVal, UpDown), Per_Yr)
            Else  ''' sample
                dblResult = SemiVar_AbsoluteSampleAnnual(DiffArr_ArithAbsolute(arrReturns, TargetVal, UpDown), Per_Yr)
            End If
        Else   ''' geometric
            If Pop_or_Sample = 0 Then ''' population
                dblResult = SemiVar_AbsolutePopulationAnnual(DiffArr_GeomAbsolute(arrReturns, TargetVal, UpDown), Per_Yr)
            Else  ''' sample
                dblResult = SemiVar_AbsoluteSampleAnnual(DiffArr_GeomAbsolute(arrReturns, TargetVal, UpDown), Per_Yr)
            End If
        End If
    End If
    SemiVariance_Absolute = dblResult * IIf(ReturnType, 1, 100)
    Erase arrReturns
    End Function

Private Function StdDev_Annual_Population(ByVal arr1, ByVal PerYr As Double) As Double
    StdDev_Annual_Population = StdDev_Population(arr1) * Sqr(IIf(PerYr, Abs(PerYr), 1))
    End Function
Private Function StdDev_Annual_Sample(ByVal arr1, ByVal PerYr As Double) As Double
    StdDev_Annual_Sample = StdDev_Sample(arr1) * Sqr(IIf(PerYr, Abs(PerYr), 1))
    End Function
Private Function StdDev_Population(ByVal arr1) As Double
    StdDev_Population = Sqr(Excel.Application.DevSq(arr1) / (UBound(arr1) - LBound(arr1) + 1))
    End Function
Private Function StdDev_Sample(ByVal arr1) As Double
    StdDev_Sample = Sqr(Excel.Application.DevSq(arr1) / (UBound(arr1) - LBound(arr1)))
    End Function
Private Function DiffArr_Arith(ByVal arr1, ByVal arr2)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = arr1(i) - arr2(i) + 1
        Next i
    DiffArr_Arith = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_Geom(ByVal arr1, ByVal arr2)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = arr1(i) / arr2(i)
        Next i
    DiffArr_Geom = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_ArithPositive(ByVal arr1, ByVal arr2)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = Excel.Application.Max(0, arr1(i) - arr2(i))
        Next i
    DiffArr_ArithPositive = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_ArithNegative(ByVal arr1, ByVal arr2)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = Excel.Application.Min(0, arr1(i) - arr2(i))
        Next i
    DiffArr_ArithNegative = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_GeomPositive(ByVal arr1, ByVal arr2)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = Excel.Application.Max(1, arr1(i) / arr2(i))
        Next i
    DiffArr_GeomPositive = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_GeomNegative(ByVal arr1, ByVal arr2)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = Excel.Application.Min(1, arr1(i) / arr2(i))
        Next i
    DiffArr_GeomNegative = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_ArithmeticAbsolute(ByVal arr1, ByVal TargetValue)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = 1 + arr1(i) - TargetValue
        Next i
    DiffArr_ArithmeticAbsolute = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_GeometricAbsolute(ByVal arr1, ByVal TargetValue)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            arrDiff(i) = (1 + arr1(i)) / (1 + TargetValue)
        Next i
    DiffArr_GeometricAbsolute = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_ArithAbsolute(ByVal arr1, ByVal TargetValue, ByVal UpDownIndicator)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            If UpDownIndicator = 0 Then ''' positive
                arrDiff(i) = Excel.Application.Max(0, arr1(i) - TargetValue) + 1
            Else  ''' negative
                arrDiff(i) = Excel.Application.Min(0, arr1(i) - TargetValue) + 1
            End If
        Next i
    DiffArr_ArithAbsolute = arrDiff
    Erase arrDiff
    End Function
Private Function DiffArr_GeomAbsolute(ByVal arr1, ByVal TargetValue, ByVal UpDownIndicator)
    Dim i As Long, arrDiff
    ReDim arrDiff(LBound(arr1) To UBound(arr1)) As Double
        For i = LBound(arr1) To UBound(arr1)
            If UpDownIndicator = 0 Then ''' positive
                arrDiff(i) = Excel.Application.Max(1, (1 + arr1(i)) / (1 + TargetValue))
            Else  ''' negative
                arrDiff(i) = Excel.Application.Min(1, (1 + arr1(i)) / (1 + TargetValue))
            End If
        Next i
    DiffArr_GeomAbsolute = arrDiff
    Erase arrDiff
    End Function
Private Function SemiVar_RelativeArithPopulation_Up(ByVal arr1, ByVal arr2)
    SemiVar_RelativeArithPopulation_Up = (StdDev_Population(DiffArr_ArithPositive(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeArithSample_Up(ByVal arr1, ByVal arr2)
    SemiVar_RelativeArithSample_Up = (StdDev_Sample(DiffArr_ArithPositive(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomPopulation_Up(ByVal arr1, ByVal arr2)
    SemiVar_RelativeGeomPopulation_Up = (StdDev_Population(DiffArr_GeomPositive(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomSample_Up(ByVal arr1, ByVal arr2)
    SemiVar_RelativeGeomSample_Up = (StdDev_Sample(DiffArr_GeomPositive(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeArithPopulation_Down(ByVal arr1, ByVal arr2)
    SemiVar_RelativeArithPopulation_Down = (StdDev_Population(DiffArr_ArithNegative(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeArithSample_Down(ByVal arr1, ByVal arr2)
    SemiVar_RelativeArithSample_Down = (StdDev_Sample(DiffArr_ArithNegative(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomPopulation_Down(ByVal arr1, ByVal arr2)
    SemiVar_RelativeGeomPopulation_Down = (StdDev_Population(DiffArr_GeomNegative(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomSample_Down(ByVal arr1, ByVal arr2)
    SemiVar_RelativeGeomSample_Down = (StdDev_Sample(DiffArr_GeomNegative(arr1, arr2))) ^ 2
    End Function
Private Function SemiVar_RelativeArithPopulation_Annual_Up(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeArithPopulation_Annual_Up = (StdDev_Annual_Population(DiffArr_ArithPositive(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_RelativeArithSample_Annual_Up(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeArithSample_Annual_Up = (StdDev_Annual_Sample(DiffArr_ArithPositive(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomPopulation_Annual_Up(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeGeomPopulation_Annual_Up = (StdDev_Annual_Population(DiffArr_GeomPositive(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomSample_Annual_Up(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeGeomSample_Annual_Up = (StdDev_Annual_Sample(DiffArr_GeomPositive(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_RelativeArithPopulation_Annual_Down(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeArithPopulation_Annual_Down = (StdDev_Annual_Population(DiffArr_ArithNegative(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_RelativeArithSample_Annual_Down(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeArithSample_Annual_Down = (StdDev_Annual_Sample(DiffArr_ArithNegative(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomPopulation_Annual_Down(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeGeomPopulation_Annual_Down = (StdDev_Annual_Population(DiffArr_GeomNegative(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_RelativeGeomSample_Annual_Down(ByVal arr1, ByVal arr2, ByVal Per_Yr)
    SemiVar_RelativeGeomSample_Annual_Down = (StdDev_Annual_Sample(DiffArr_GeomNegative(arr1, arr2), Abs(Per_Yr))) ^ 2
    End Function
Private Function SemiVar_AbsolutePopulation(ByVal arr1)
    SemiVar_AbsolutePopulation = StdDev_Population(arr1) ^ 2
    End Function
Private Function SemiVar_AbsoluteSample(ByVal arr1)
    SemiVar_AbsoluteSample = StdDev_Sample(arr1) ^ 2
    End Function
Private Function SemiVar_AbsolutePopulationAnnual(ByVal arr1, ByVal Per_Yr)
    SemiVar_AbsolutePopulationAnnual = StdDev_Annual_Population(arr1, Abs(Per_Yr)) ^ 2
    End Function
Private Function SemiVar_AbsoluteSampleAnnual(ByVal arr1, ByVal Per_Yr)
    SemiVar_AbsoluteSampleAnnual = StdDev_Annual_Sample(arr1, ByVal Per_Yr) ^ 2
    End Function
Private Function _
DataToArray_WealthRatio _
    ( _
        ByVal ReturnsIn, _
        ByVal InputType As Byte, _
        ByVal MissingDataEqualsZero As Boolean _
    )
    
    Dim RngCell As Range, _
        arrResults, _
        arrTemp, _
        InputFactor, _
        Counter As Long, _
        i As Long
        
        InputFactor = IIf(InputType = 0, 100, 1)
        
        If TypeOf ReturnsIn Is Range Then
            arrResults = Range_to_Array(ReturnsIn)
            If MissingDataEqualsZero Then
                For i = LBound(arrResults) To UBound(arrResults)
                    If IsNumeric(arrResults(i)) Then
                        arrResults(i) = 1 + CDbl(arrResults(i)) / InputFactor
                    Else
                        arrResults(i) = 1
                    End If
                Next i
            Else
                ReDim arrTemp(LBound(arrResults) To UBound(arrResults))
                For i = LBound(arrResults) To UBound(arrResults)
                    If IsEmpty(arrResults(i)) Then
                        ''' do nothing
                    ElseIf Excel.Application.IsText(arrResults(i)) Then
                        ''' do nothing
                    Else
                        Counter = Counter + 1
                        arrTemp(Counter) = 1 + CDbl(arrResults(i)) / InputFactor
                    End If
                Next i
                If Counter Then
                    ReDim Preserve arrTemp(1 To Counter)
                    arrResults = arrTemp
                End If
            End If
        Else
            If TypeName(ReturnsIn) = "Double" Then
                ReDim arrResults(1 To 1) As Double
                Counter = Counter + 1
                arrResults(Counter) = 1 + ReturnsIn / InputFactor
            Else
                If ArrayDimensions(ReturnsIn) > 1 Then
                    ReturnsIn = Excel.Application.Transpose(ReturnsIn)
                    Counter = UBound(ReturnsIn) - LBound(ReturnsIn) + 1
                End If
                ReDim ReturnsArray(1 To UBound(ReturnsIn) - LBound(ReturnsIn) + 1)
                For i = LBound(ReturnsIn) To UBound(ReturnsIn)
                    If IsNumeric(ReturnsIn(i)) Then
                        Counter = Counter + 1
                        ReturnsArray(Counter) = 1 + ReturnsIn(i) / InputFactor
                    End If
                Next i
            End If
        End If
    If Counter Then ReDim Preserve arrResults(1 To Counter)
    DataToArray_WealthRatio = arrResults
    Erase arrResults
    End Function
Private Function _
DataToArray _
    ( _
        ByVal ReturnsIn, _
        ByVal InputType As Byte, _
        ByVal MissingDataEqualsZero As Boolean _
    )
    
    Dim RngCell As Range, _
        arrResults, _
        arrTemp, _
        InputFactor, _
        Counter As Long, _
        i As Long
        
        InputFactor = IIf(InputType = 0, 100, 1)
        
        If TypeOf ReturnsIn Is Range Then
            arrResults = Range_to_Array(ReturnsIn)
            If MissingDataEqualsZero Then
                For i = LBound(arrResults) To UBound(arrResults)
                    If IsNumeric(arrResults(i)) Then
                        arrResults(i) = CDbl(arrResults(i)) / InputFactor
                    Else
                        arrResults(i) = 0
                    End If
                Next i
            Else
                ReDim arrTemp(LBound(arrResults) To UBound(arrResults))
                For i = LBound(arrResults) To UBound(arrResults)
                    If IsEmpty(arrResults(i)) Then
                        ''' do nothing
                    ElseIf Excel.Application.IsText(arrResults(i)) Then
                        ''' do nothing
                    Else
                        Counter = Counter + 1
                        arrTemp(Counter) = CDbl(arrResults(i)) / InputFactor
                    End If
                Next i
                If Counter Then
                    ReDim Preserve arrTemp(1 To Counter)
                    arrResults = arrTemp
                End If
            End If
        Else
            If TypeName(ReturnsIn) = "Double" Then
                ReDim arrResults(1 To 1) As Double
                Counter = Counter + 1
                arrResults(Counter) = ReturnsIn / InputFactor
            Else
                If ArrayDimensions(ReturnsIn) > 1 Then
                    ReturnsIn = Excel.Application.Transpose(ReturnsIn)
                    Counter = UBound(ReturnsIn) - LBound(ReturnsIn) + 1
                End If
                ReDim ReturnsArray(1 To UBound(ReturnsIn) - LBound(ReturnsIn) + 1)
                For i = LBound(ReturnsIn) To UBound(ReturnsIn)
                    If IsNumeric(ReturnsIn(i)) Then
                        Counter = Counter + 1
                        ReturnsArray(Counter) = ReturnsIn(i) / InputFactor
                    End If
                Next i
            End If
        End If
    If Counter Then ReDim Preserve arrResults(1 To Counter)
    DataToArray = arrResults
    Erase arrResults
    End Function
Private Function _
Range_to_Array _
    ( _
        ByVal Rng As Range _
    )

    Dim ReturnArray, _
        arrTemp, _
        i As Long, _
        j As Long, _
        k As Long
    
    If Rng.Cells.Count = 1 Then
        ReDim ReturnArray(1 To 1)
        ReturnArray(1) = Rng
    Else
        If Rng.Columns.Count > 1 And Rng.Rows.Count > 1 Then
            arrTemp = Rng.Value
            ReDim ReturnArray(1 To UBound(arrTemp, 1) * UBound(arrTemp, 2))
            k = 0
            For i = 1 To UBound(arrTemp, 1)
                For j = 1 To UBound(arrTemp, 2)
                    k = k + 1
                    ReturnArray(k) = arrTemp(i, j)
                Next j
            Next i
            Erase arrTemp
        Else
            With Excel.Application
                ReturnArray = .Transpose(Rng)
                If Rng.Rows.Count = 1 Then ReturnArray = .Transpose(ReturnArray)
            End With
        End If
    End If
    
    Range_to_Array = ReturnArray
    Erase ReturnArray
    End Function

Function _
ArrayDimensions _
    ( _
        InputArray As Variant _
    ) As Long
    
    Dim n As Long

    If Not IsArray(InputArray) Or IsObject(InputArray) Then
        n = 0
        Exit Function
    Else
        n = 1
    End If

    On Error Resume Next

    Do
        n = n + 1
    Loop While (LBound(InputArray, n) <= UBound(InputArray, n))

    ArrayDimensions = n - 1
End Function
 
Upvote 0
From a search of Google for the definition of semi-variance...

if your data are in A1:A25 and your threshold is in C1, use the array formula
=VAR(IF(A1:A25<$C$1,A1:A25,0))

To complete an array formula, do *not* use the ENTER key. Instead use the CTRL+SHIFT+ENTER combination.

For the threshold, you can either enter a number such as zero in C1, or enter a formula such as =AVERAGE(A1:A25)

I imagine you would get the same result from an appropriate use of Bill's solution but I won't be taking the time to figure out which one. {grin}
 
Upvote 0
That is certainly a much, much easier way to do it, as opposed to the Rube Goldberg VBA creation. :LOL:

The VAR[P] solution is fine for cumulative, unannualized returns. My idea was to allow for easier switching between cumulative and annualized variablility, plus allow for the ease of changing between population and sample and the compounding periods per year. For the relative semi-variance, the geometric vs. arithmetic difference can be important, too.

The VBA was originally a way to make it simple. Over time, I decided to incorporate every possible option in one UDF. Clearly, there is just a little bloat in there. :LOL: You should see the options I have for the Information Ratio!
 
Upvote 0
Just an FYI, the Semi-Variance UDF produces the semi-variance of the excess returns, rather than the semi-variance of the actual returns.

Tushar's result (using population rather than sample):
=VARP(IF(A1:A25<$C$1,A1:A25,0))

The UDF result in its Excel equivalent
=VARP(IF(A1:A25<$C$1,A1:A25-$C$1,0))

Actually, I think I am off by a factor of 100 in some of the options.
 
Upvote 0
Guys, thanks for your help. I'm going to study the differences between the 2 approaches and make sure I understand them. I'll keep you posted if I have any questions. Thanks again!
 
Upvote 0
Here is the primary difference....

Suppose you have hurdle rate of 5% and a return of -3%. With Tushar's answer, the data point included is -3%. With the UDF, the data point included is 8% (+5% - (-3%)). The sign does not matter since we are squaring the values.

Tushar's result is the downside variance, whereas mine is the excess downside variance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Formula to Calculate Weighted Variance % Between Two DataSets
Replies
0
Views
271
acool
A
S
  • Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
132
strugglingaccountant
S
J
  • Question
Variance if both cells are zero or if > zero
2
Replies
10
Views
520
JustDebbie5
J
A
  • Solved
Drop-down menu entered semi-automatic excel 2019 is it possible?
2 3
Replies
24
Views
808
Peter_SSs
Peter_SSs
M
  • Question
Conditional formatting on revenue variances
Replies
8
Views
833
awoohaw
awoohaw

Forum statistics

Threads
1,215,280
Messages
6,124,034
Members
449,139
Latest member
sramesh1024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top