Thread: Using loops to find averages

Paste

=AVERAGEIF(A:A,D2,C:C)

into E2 and copy down.

Hi Mdiver, click the button Calculate Averages to see if this meets your requirements.

Hi Mdiver, I am not sure without specifics how you could use the code I provided to fit your needs but I'll try to explain what's going on. for

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''If the cl value = Select Case "a" or "a" from your list in Column A'''
'''then go into Case "a"'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Case "a"
     '''Force() is an array that is used to add all the case values
     '''Force(1) is the first array count and is designated to case "a", Force(2) is 
     '''designated to case "b" and so on.
     Force(1) = Force(1) + cl.Offset(0, 2).Value
     '''a1 is the counter for case "a" so that we know what the count
     '''is regarding the amount of times Case "a" is selected.
     a1 = a1 + 1
      '''Set Range E2 value to equal the average'''
      Range("E2").Value = Force(1) / a1

Originally Posted by Mdiver

What does the For Next loop do that you put in the macro? Does this allow me to put any units I want or just 1-10?

For x = 1 To 10
Force(x) = 0
Next x

That represents the count of letters ie: a to j, that you have on the sheet. Force(x [where x = 1]) represents "a" while Force(x [where x = 10]) represents "j"

If you elaborate more from your post #4 I can help you figure it all out (hopefully)

I commented this out some more for you, let me know if something isn't clear enough for you.

Sub Mdiver()
    Dim SpotRng As Range, Force(10) As Double, AvgForce As Double, rCnt As Long
    Dim a1 As Long, b1 As Long, c1 As Long, d1 As Long, e1 As Long, f1 As Long, g1 As Long
    Dim h1 As Long, i1 As Long, j1 As Long
    
    '''setting a1 to j1 to 0 values so the are countable (Excel does this by default but
    '''I like to make sure'''
    a1 = 0: b1 = 0: c1 = 0: d1 = 0: e1 = 0: f1 = 0: g1 = 0: h1 = 0: i1 = 0: j1 = 0
    
    '''Setting the Force Array to 0's to be addable'''
    For x = 1 To 10
        Force(x) = 0
    Next x
    '''Find the last row count'''
    rCnt = Range("A1000").End(xlUp).Row
    
    '''Set SpotRng is necessary for the loop to know what range of cells
    '''to loop through'''
    Set SpotRng = Sheets(1).Range("A1", Range("A1000").End(xlUp))
    
    '''Loop through each Cell in SpotRng'''
    For Each cl In SpotRng
        Select Case cl
        
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            '''If the cl value = Select Case "a" or "a" from your list in Column A'''
            '''then go into Case "a"'''''''''''''''''''''''''''''''''''''''''''''''''
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            Case "a"
                '''Force() is an array that is used to add all the case values
                Force(1) = Force(1) + cl.Offset(0, 2).Value
                '''a1 is the counter for case "a" so that we know what the count
                '''is regarding the amount of times Case "a" is selected.
                a1 = a1 + 1
                '''Set Range E2 value to equal the average'''
                Range("E2").Value = Force(1) / a1
            Case "b"
                '''Same reasoning as Case "a"'''
                Force(2) = Force(2) + cl.Offset(0, 2).Value
                b1 = b1 + 1
                Range("E3").Value = Force(2) / b1
            Case "c"
                '''Same reasoning as Case "a"'''
                Force(3) = Force(3) + cl.Offset(0, 2).Value
                c1 = c1 + 1
                Range("E4").Value = Force(3) / c1
            Case "d"
                '''Same reasoning as Case "a"'''
                Force(4) = Force(4) + cl.Offset(0, 2).Value
                d1 = d1 + 1
                Range("E5").Value = Force(4) / d1
            Case "e"
                '''Same reasoning as Case "a"'''
                Force(5) = Force(5) + cl.Offset(0, 2).Value
                e1 = e1 + 1
                Range("E6").Value = Force(5) / e1
            Case "f"
                '''Same reasoning as Case "a"'''
                Force(6) = Force(6) + cl.Offset(0, 2).Value
                f1 = f1 + 1
                Range("E7").Value = Force(6) / f1
            Case "g"
                '''Same reasoning as Case "a"'''
                Force(7) = Force(7) + cl.Offset(0, 2).Value
                g1 = g1 + 1
                Range("E8").Value = Force(7) / g1
            Case "h"
                '''Same reasoning as Case "a"'''
                Force(8) = Force(8) + cl.Offset(0, 2).Value
                h1 = h1 + 1
                Range("E9").Value = Force(8) / h1
            Case "i"
                '''Same reasoning as Case "a"'''
                Force(9) = Force(9) + cl.Offset(0, 2).Value
                i1 = i1 + 1
                Range("E10").Value = Force(9) / i1
            Case "j"
                '''Same reasoning as Case "a"'''
                Force(10) = Force(10) + cl.Offset(0, 2).Value
                j1 = j1 + 1
                Range("E11").Value = Force(10) / j1
        End Select
    Next cl
End Sub

Also, I'm sure that a VBA guru could make this code more efficient but it does work on any number of entries.

Wowzers I am having a hard time understanding this now . I am going to try and walk through this with you but it may not happen in one day as it looks like there are a few steps that need to be done here and I don't know if I have the coding abilities to do everything you want but I will try (and get help if I need it).