using Average function to populate in price column on form

Hi again Leopard

Your line:

b(y, 6) = Format(Application.WorksheetFunction.Average(a(i, 6)), "#,##0.00")

doesn't make much sense- it tries to average a single number (which produces the number itself) and so it just gives a text version of that number. In fact, if there's more than one  match for a given (perhaps partial) "BRAND",  it just overwrites b(y, 6) with a text version of the last UNIT PRICE for that "BRAND"

REVISION #1, 19 February 2025

In the attached modified file, the code is changed so that:

1.  the ListBox now:
   • includes a row of titles
   • ignores CLIENT NO and INVOICE NO columns
   • provides two average prices- see below
   • has columns adjusted manually via its ColumnWidths property
2. it corrects an error whereby the line  y = dic(ky) incorrectly set the row.

See the changes in bold below (with comments for your understanding):

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim i As Long, j As Long, k As Long, y As Long
  Dim dic As Object
  Dim ky As Variant

  Set dic = CreateObject("Scripting.Dictionary")

  ListBox1.Clear
' add row for titles
  ReDim b(1 To UBound(a, 1) + 1, 1 To UBound(a, 2))
  ' write titles
    b(1, 1) = "Item"
    b(1, 2) = "Brand"
    b(1, 3) = "Transactions"
    b(1, 4) = "Total Quantity"
    b(1, 5) = "Total Value"
    b(1, 6) = "Average Transaction price"
    b(1, 7) = "Average Unit price"

  For i = 1 To UBound(a)
    If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1

    If UCase(a(i, 4)) Like "*" & UCase(txt1) & "*" Then
      ky = a(i, 4)

      If Not dic.exists(ky) Then
        k = k + 1
        ' use a dictionary item counter other than y
        j = j + 1
        dic(ky) = j
      End If

      y = dic(ky)

      ' write in rows below first (titles)
      b(y + 1, 1) = y
      b(y + 1, 2) = a(i, 4) 'Brand
      b(y + 1, 3) = b(y + 1, 3) + 1 'number of transactions in this element
      b(y + 1, 4) = Format(CDec(b(y + 1, 4)) + a(i, 5), "#,##0.00") ' total Qty
      ' add to total value
      b(y + 1, 5) = Format(CDec(b(y + 1, 5)) + a(i, 5) * a(i, 6), "#,##0.00")
      ' see if this is the first price for dictionary item
      If b(y + 1, 6) = "" Then
        ' if so, just capture first price
        b(y + 1, 6) = Format(a(i, 6), "#,##0.00")
        Else
        ' otherwise convert existing entry, add to new value then calculate average
        b(y + 1, 6) = Format((CDec(b(y + 1, 6)) * (b(y + 1, 3) - 1) + a(i, 6)) / b(y + 1, 3), "#,##0.00")
      End If
      ' write unit average price
      b(y + 1, 7) = Format(CDec(b(y + 1, 5)) / CDec(b(y + 1, 4)), "#,##0.00")
    End If
  Next
 If k > 0 Then ListBox1.List = b

End Sub

This will gives both the average of the UNIT PRICE used in each matching transaction or row in your worksheet and the average price of the units sold (total price divided by total quantity- see more detail in the Discussion below this Answer).

Hope this helps.

Hi leopard,

I'm not sure if this is the cause of your problem but I'm going to take a guess based on what I see with your code.

You have the variable " a " equal a range (A2:G10). You should declare "a" as a Range object and then use Set a =.

Then when the userform is initialized both TextBox1 and ListBox1 are empty. 

Then in your formula You have Average looking at A2:G10 ( a ). You should be specific and state which column you want to average (E, F, or G) - something like: Average(F2:F & Range("D" & Rows.Count).End(3), (Range("D" & Rows.Count).End(3)-1))

Hope this helps

Cheers   :-)