Text Form Field Expression - Calculate Average but Ignore '0' or 'N/A'

Question

Good Day All,&nbsp;I am making a Job Review Word form that is based off a numerical ratings system. I would like to take the average of all the numerical ratings, currently =Average(ADP,Fit,TimeAway,Calendar) is working great. (see pic)&nbsp;What I would like to do is calculate the average of the ratings but ignore any values that say 0 or N/A. In excel I would be using the AVERAGEIF function, but I cannot get that to work here.&nbsp;Does anyone have a solution that I am missing?&nbsp;

kidd_ip · Answer

Try this:
&nbsp;
Sub CalculateAverage()
    Dim ADP As Double
    Dim Fit As Double
    Dim TimeAway As Double
    Dim Calendar As Double
    Dim Sum As Double
    Dim Count As Integer

' Assign values from form fields
    ADP = CDbl(ActiveDocument.FormFields("ADP").Result)
    Fit = CDbl(ActiveDocument.FormFields("Fit").Result)
    TimeAway = CDbl(ActiveDocument.FormFields("TimeAway").Result)
    Calendar = CDbl(ActiveDocument.FormFields("Calendar").Result)

' Initialize sum and count
    Sum = 0
    Count = 0

' Check each value and add to sum if it's valid
    If ADP &gt; 0 Then
        Sum = Sum + ADP
        Count = Count + 1
    End If
    If Fit &gt; 0 Then
        Sum = Sum + Fit
        Count = Count + 1
    End If
    If TimeAway &gt; 0 Then
        Sum = Sum + TimeAway
        Count = Count + 1
    End If
    If Calendar &gt; 0 Then
        Sum = Sum + Calendar
        Count = Count + 1
    End If

' Calculate average
    If Count &gt; 0 Then
        ActiveDocument.FormFields("Average").Result = Format(Sum / Count, "0.00")
    Else
        ActiveDocument.FormFields("Average").Result = "N/A"
    End If
End Sub

&nbsp;

gasdetect · Answer

This looks great!I'm sorry for this rookie question but... where would I post this formula exactly?&nbsp;

gasdetect · Answer

Where would I insert this formula?

Forum Discussion

Text Form Field Expression - Calculate Average but Ignore '0' or 'N/A'

Share

Resources