Introduction to Custom Excel VBA Functions for Averaging Non-Zero Values

When dealing with large datasets in Excel, it's often necessary to calculate the average of specific values within a given date range, while excluding zeros. This article explores how to create a custom VBA function that can be used to achieve this, focusing on versions of Excel starting from 2010.

Problem Statement

The original problem involved calculating the average of the three largest values within a specified date range. However, the solution provided had limitations: it required at least 3 values, and it did not exclude zeros. As a result, this article presents an improved solution using a custom VBA function that averages the largest values while ignoring zeros.

Existing Solution Limitations

The initial solution worked only if there were 3 or more values within the date range, as shown in the formula:

AVERAGELARGEIFYEARDatesYEARA1IFMONTHDatesMONTHA1Values{123}

If fewer than 3 values existed, zeros were included in the average, which introduces inaccuracies. This issue necessitates creating a more robust custom function.

New Custom Function: AVERAGE_NZLARGEIF

To address the limitations, a custom VBA function can be created. This function, named AVERAGE_NZLARGEIF, takes several parameters to ensure accuracy, particularly in versions of Excel starting from 2010. Here's how to implement it:

Custom VBA Code

Add the following VBA code to the worksheet module:

' Custom VBA function to average the largest non-zero values within a date range
Public Function AVERAGE_NZLARGEIF(YEARDates As Range, MONTHDates As Range, Values As Range, Optional NumberLargest As Long  3) As Double
    Dim i As Long
    Dim count As Long
    Dim sum As Double
    Dim largestValues As Collection
    Dim largestValue As Long
    Set largestValues  New Collection
    For i  1 To 
        If Yeardates(i, 1)  YEARA1 And MONTHDates(i, 1)  MONTHA1 Then
            largestValue  Values(i, 1)
            If largestValue  0 Then
                Do While   NumberLargest
                     largestValue, CStr(largestValue)
                Loop
            End If
            count  count   1
        End If
    Next i
    If count  0 Then
        sum  (largestValues)
        AVERAGE_NZLARGEIF  sum
    Else
        AVERAGE_NZLARGEIF  0
    End If
End Function

This VBA function, AVERAGE_NZLARGEIF, iterates through the specified range, checks for values within the given date range, and only includes non-zero values in the result. It then averages the specified number of largest values, ensuring that fewer than the specified number of values is handled gracefully.

Creating Helper Cells for Lookup

Although the custom function simplifies the process, you might still need to use helper cells to find the largest non-zero values. Here's how to do it:

Helper Cell for 3rd Largest Value:

AVERAGELARGEIFYEARDatesYEARA4IFMONTHDatesMONTHA4Values3

Helper Cell for 2nd Largest Value:

AVERAGELARGEIFYEARDatesYEARA4IFMONTHDatesMONTHA4Values2

Helper Cell for 1st Largest Value:

AVERAGELARGEIFYEARDatesYEARA4IFMONTHDatesMONTHA4Values1

Once the helper cells are created, use the following array formula to calculate the average:

AVERAGEIFD1:F1

However, using a custom VBA function can provide a more streamlined and elegant solution.

Advanced Custom VBA Function: AVERAGE_NZLARGEIFY

To further simplify the process, you can create a custom VBA function that lists the largest values and calculates the average:

' Custom VBA function to list and average the largest non-zero values within a date range
Public Function AVERAGE_NZLARGEIFY(YEARDates As Range, MONTHDates As Range, Values As Range, Optional NumberLargest As Long  3) As Variant
    Dim i As Long
    Dim count As Long
    Dim largestValues() As Double
    ReDim largestValues(1 To NumberLargest)
    Dim sum As Double
    Dim largestValue As Double
    For i  1 To 
        If Yeardates(i, 1)  YEARA1 And MONTHDates(i, 1)  MONTHA1 Then
            largestValue  Values(i, 1)
            If largestValue  0 Then
                Do While count  NumberLargest
                    If largestValue  largestValues(count) Then
                        largestValues(count)  largestValue
                        ReDim Preserve largestValues(1 To count   1)
                        Exit Do
                    End If
                    count  count   1
                Loop
                sum  sum   largestValue
            End If
        End If
    Next i
    If count  0 Then
        sum  sum / count
        AVERAGE_NZLARGEIFY  sum
    Else
        AVERAGE_NZLARGEIFY  0
    End If
End Function

This function, AVERAGE_NZLARGEIFY, lists the largest non-zero values in a specified date range and calculates the average. Here's how to use it:

Insert year-month values in Column A. Use a formula in Column B to get the average of the largest values:

AVERAGE_NZLARGEIFY(YEARDates, MONTHDates, Values, 3)

Conclusion

Creating custom VBA functions in Excel allows you to build powerful and flexible solutions. By using the AVERAGE_NZLARGEIF or AVERAGE_NZLARGEIFY functions, you can easily average the largest non-zero values within specified date ranges, ensuring accurate and efficient data analysis. This approach is particularly useful in versions of Excel starting from 2010, and it streamlines the process compared to using multiple helper cells.