CODEWITHSUNDEEP
vbaexcel
user5738346
user5738346

Reputation:

How to get average by cell content on VBA

In excel, I have column 1 with tickers, and column 2 with numbers, like this:

      A  B    
1    AAA 10
2    AAA 12
3    AAA 14
4    BBB  9
5    BBB 10
6    BBB 11

I need a piece of code to calculate average BY TICKER, which means that in this case I would have AAA average : 12 and BBB average = 10, etc etc etc. Up to now all i got is this code which tries to calculate the sums, I will do the divisions later, but something's wrong: 

For row = 2 to 6
Ticker = Cells(row - 1, 1)
If Cells(row, 1) = Cells(row - 1, 1) Then
sum = sum + Cells(row, 2)
Else
Cells(row, 6) = sum
sum = 0
row = row + 1
Next

I get an error saying "For is missing"

Upvotes: 2

Views: 373

Answers (3)

Dirk Reichel
Dirk Reichel

Reputation: 7979

To extend the answer from Jeeped and do it with a list which is not ordered, you also could do it like this:

C1:       =A1
C2:       =IF(LEN(C1),IFERROR(INDEX(A:A,MATCH(1,(COUNTIF(C$1:C1,A$1:A$1000)=0)*(A$1:A$1000<>""),0)),""),"")}
C3....Cn: copy down from C2

D1:      =IF(LEN(C1),AVERAGEIF(A:A,C1,B:B),"")
D2...Dn: copy down from D1

C2 is an array formula and needs to be confirmed with Ctrl+Shift+Enter

to do it via VBA (should be faster than my formula for really big tables) you can use something like that: (put this in a "Module" in the VBA-Window, the same like your recorded macros ar written)

Option Explicit

Public Function getAllAvg(rng As Range) As Variant
  Set rng = Intersect(rng.Parent.UsedRange, rng)

  Dim varInput As Variant
  varInput = rng.Value

  Dim varOutput() As Variant
  ReDim varOutput(1 To UBound(varInput), 1 To 2)
  varOutput(1, 1) = ""

  Dim i As Long, j As Long
  For i = 1 To UBound(varInput)
    If Len(varInput(i, 1)) Then
      j = 1

      While Len(varOutput(j, 1)) And (varOutput(j, 1) <> varInput(i, 1)) And (j < UBound(varOutput))
        j = j + 1
      Wend

      If Len(varOutput(j, 1)) = 0 Then
        varOutput(j, 1) = varInput(i, 1)
        varOutput(j, 2) = Application.AverageIf(rng.Columns(1), varOutput(j, 1), rng.Columns(2))
        varOutput(j + 1, 1) = ""
      End If

    End If
  Next

  While Len(varOutput(j, 1)) And (j < UBound(varOutput))
  j = j + 1
  Wend

  If Len(varOutput(j, 1)) = 0 Then
    For i = j To UBound(varOutput)
      varOutput(i, 1) = ""
      varOutput(i, 2) = ""
    Next
  End If
  getAllAvg = varOutput
End Function

then select a range like C2:D12 and enter:

=getAllAvg(A:B)

and confirm with Ctrl+Shift+Enter. it will directly output the whole list (and recalculate if needed)

EDIT:

If your list is always in a sorted order, you also could use this code:

Option Explicit

Public Function getAllAvgSorted(rng As Range) As Variant
  Set rng = Intersect(rng.Parent.UsedRange, rng)

  Dim varInput As Variant
  varInput = rng.Value

  Dim varOutput() As Variant
  ReDim varOutput(1 To UBound(varInput), 1 To 2)
  varOutput(1, 1) = ""

  Dim i As Long, j As Long
  j = 1

  For i = 1 To UBound(varInput)
    If Len(varInput(i, 1)) Then
      If varOutput(j, 1) <> varInput(i, 1) Then
        If Len(varOutput(j, 1)) Then j = j + 1
        varOutput(j, 1) = varInput(i, 1)
        varOutput(j, 2) = Application.AverageIf(rng.Columns(1), varOutput(j, 1), rng.Columns(2))
      End If
    End If
  Next

  While j < UBound(varOutput)
    j = j + 1
    varOutput(j, 1) = ""
    varOutput(j, 2) = ""
  Wend

  getAllAvgSorted = varOutput
End Function

Upvotes: 0

AlShibli
AlShibli

Reputation: 1

In your code:

  • sum ignored the first AAA
  • missed the End If
  • incremented row twice, once by row = row+1 and then by next
  • and some other not used variables

Try this:

Prev = "***"
For row = 1 to 6
  If Cells(row, 1) = prev Then
    sum = sum + Cells(row, 2)
  Else
    Cells(row, 6) = sum
    sum = 0
  End If
  prev = Cells(row,1)
Next

Upvotes: 0

user4039065
user4039065

Reputation:

Maybe something like this in C1.

=IF(A1<>A2,AVERAGEIF(A:A,A1,B:B),"")

        averageif_once

Upvotes: 4

Related Questions