Forum Discussion

GasDetect's avatar
GasDetect
Copper Contributor
Jan 10, 2025

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

Good Day All, 

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) 

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. 

Does anyone have a solution that I am missing?

 

  • GasDetect's avatar
    GasDetect
    Copper Contributor

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

  • Try this:

     

    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 > 0 Then
            Sum = Sum + ADP
            Count = Count + 1
        End If
        If Fit > 0 Then
            Sum = Sum + Fit
            Count = Count + 1
        End If
        If TimeAway > 0 Then
            Sum = Sum + TimeAway
            Count = Count + 1
        End If
        If Calendar > 0 Then
            Sum = Sum + Calendar
            Count = Count + 1
        End If
    
        ' Calculate average
        If Count > 0 Then
            ActiveDocument.FormFields("Average").Result = Format(Sum / Count, "0.00")
        Else
            ActiveDocument.FormFields("Average").Result = "N/A"
        End If
    End Sub
    

     

Resources