Average of Multiple Fields

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jl2886
    New Member
    • Mar 2007
    • 83

    #1

    Average of Multiple Fields

    I have four fields in my table that I want to average providing the fields have a value, not the null, for each case(id). I also have an average field in my table. I have a form for the table and I want the average value to be populated on click with the average of the four fields. How can I do this?
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    Code:
    Private Sub ButtonName_Click()
    Dim count As Integer
    Dim TotalValue
    
       count = 0
       TotalValue = 0
       If nz(Me![Field1],0) <> 0 Then
    	  count = count + 1
    	  TotalValue = TotalValue + Me![Field1]
       ElseIf nz(Me![Field2],0) <> 0 Then
    	  count = count + 1
    	  TotalValue = TotalValue + Me![Field2]
       ElseIf nz(Me![Field3],0) <> 0 Then
     	  count = count + 1
     	  TotalValue = TotalValue + Me![Field3]
       ElseIf nz(Me![Field4],0) <> 0 Then
     	  count = count + 1
     	  TotalValue = TotalValue + Me![Field4]
       End If
    
       Me!AvgValue = TotalValue / count
    
    End Sub
    I'm sure theres a more efficient way of doing this but can't think of one at the moment.

    Mary

    Comment

    • jl2886
      New Member
      • Mar 2007
      • 83

      #3
      Thanks Mary, but this doesn't work. It only reports the first Field value. I think it is because the code reads If Field 1 not null then count=1 and Total value=Field1 and ends there. Thus, if field 1 is empty, then it yields the field 2 value. Maybe I didn't explain myself right. I want it to average all the values, but only if the value is not null.
      Here is the code with my variables, with mmcarthy's code.
      Code:
      Private Sub AVERAGELE_Click()
      Dim count As Integer
      Dim TotalValue
         count = 0
         TotalValue = 0
         If Nz(Me.Ctl21st, 0) <> 0 Then
            count = count + 1
            TotalValue = TotalValue + Me.Ctl21st
         ElseIf Nz(Me.AVS, 0) <> 0 Then
            count = count + 1
            TotalValue = TotalValue + Me.AVS
         ElseIf Nz(Me.Fasano, 0) <> 0 Then
            count = count + 1
            TotalValue = TotalValue + Me.Fasano
         ElseIf Nz(Me.EMSI, 0) <> 0 Then
            count = count + 1
            TotalValue = TotalValue + Me.EMSI
          ElseIf Nz(Me.ISC, 0) <> 0 Then
            count = count + 1
            TotalValue = TotalValue + Me.ISC
         End If
         Me.AVERAGELE = TotalValue / count
      End Sub
      Originally posted by mmccarthy
      Code:
      Private Sub ButtonName_Click()
      Dim count As Integer
      Dim TotalValue
      
         count = 0
         TotalValue = 0
         If nz(Me![Field1],0) <> 0 Then
      	  count = count + 1
      	  TotalValue = TotalValue + Me![Field1]
         ElseIf nz(Me![Field2],0) <> 0 Then
      	  count = count + 1
      	  TotalValue = TotalValue + Me![Field2]
         ElseIf nz(Me![Field3],0) <> 0 Then
       	  count = count + 1
       	  TotalValue = TotalValue + Me![Field3]
         ElseIf nz(Me![Field4],0) <> 0 Then
       	  count = count + 1
       	  TotalValue = TotalValue + Me![Field4]
         End If
      
         Me!AvgValue = TotalValue / count
      
      End Sub
      I'm sure theres a more efficient way of doing this but can't think of one at the moment.

      Mary

      Comment

      • MMcCarthy
        Recognized Expert MVP
        • Aug 2006
        • 14387

        #4
        Sorry I shouldn't do things when I'm tired. Try this ...
        Code:
        Private Sub AVERAGELE_Click()
        Dim count As Integer
        Dim TotalValue
           count = 0
           TotalValue = 0
        
           If Nz(Me.Ctl21st, 0) <> 0 Then
              count = count + 1
              TotalValue = TotalValue + Me.Ctl21st
           End If
        
           If Nz(Me.AVS, 0) <> 0 Then
              count = count + 1
              TotalValue = TotalValue + Me.AVS
           End If
        
           If Nz(Me.Fasano, 0) <> 0 Then
              count = count + 1
              TotalValue = TotalValue + Me.Fasano
           End If
        
           If Nz(Me.EMSI, 0) <> 0 Then
              count = count + 1
              TotalValue = TotalValue + Me.EMSI
           End If
        
            If Nz(Me.ISC, 0) <> 0 Then
              count = count + 1
              TotalValue = TotalValue + Me.ISC
           End If
        
           Me.AVERAGELE = TotalValue / count
        End Sub
        Mary

        Comment

        • jl2886
          New Member
          • Mar 2007
          • 83

          #5
          Hhahaha, I ended up figuring it out and doing the same thing as you.

          Originally posted by mmccarthy
          Sorry I shouldn't do things when I'm tired. Try this ...
          Code:
          Private Sub AVERAGELE_Click()
          Dim count As Integer
          Dim TotalValue
             count = 0
             TotalValue = 0
          
             If Nz(Me.Ctl21st, 0) <> 0 Then
                count = count + 1
                TotalValue = TotalValue + Me.Ctl21st
             End If
          
             If Nz(Me.AVS, 0) <> 0 Then
                count = count + 1
                TotalValue = TotalValue + Me.AVS
             End If
          
             If Nz(Me.Fasano, 0) <> 0 Then
                count = count + 1
                TotalValue = TotalValue + Me.Fasano
             End If
          
             If Nz(Me.EMSI, 0) <> 0 Then
                count = count + 1
                TotalValue = TotalValue + Me.EMSI
             End If
          
              If Nz(Me.ISC, 0) <> 0 Then
                count = count + 1
                TotalValue = TotalValue + Me.ISC
             End If
          
             Me.AVERAGELE = TotalValue / count
          End Sub
          Mary

          Comment

          • MMcCarthy
            Recognized Expert MVP
            • Aug 2006
            • 14387

            #6
            Originally posted by jl2886
            Hhahaha, I ended up figuring it out and doing the same thing as you.
            It feels good to figure it out though :)

            Comment

            Working...