Selected Answer

Leopard

*Average* is an Excel worksheet function (not an Application feature) so your line:

`TextBox6.Text = Format(`**Application.**Average(MyPrAv), "#,##0.00")

should read:

`TextBox6.Text = Format(`**WorksheetFunction**.Average(MyPrAv), "#,##0.00")

**BUT** your variable *MyPrAv* is a single number, accumulated via the line:

`If j = 6 Then MyPrAv = MyPrAv + a(i, j)`

so it make no sense to average that, a single number.

You probably need to count the number of matches and (assuming that is>0) divide the accumulated number by that count.

**Revision #1 (17 June 2024)**

Looking at this again, there's no need for the variable *MyPrAv* the avarage price paid can be caculaded from existing variables *MyTotal* and *MyTotall* as follow (changes in bold and withing the attached revised file)

```
Sub FilterData()
Dim lindex&
Dim MyTotal
```**As Double,** MyTotal1 **As Double ', MyPrAv As Double ' don't use**
Dim fDate As Date, tDate As Date
Dim txt1 As String, txt2 As String, txt3 As String
Dim i As Long, j As Long, k As Long, iMax As Long
ListBox1.Clear: MyTotal = 0
On Error Resume Next
fDate = DateValue(TextBox2.Text)
tDate = DateValue(TextBox3.Text)
On Error GoTo 0
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a)
If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
If (LCase(a(i, 4)) Like LCase(txt1) & "*") And ((tDate = 0 And fDate = 0) Or (DateValue(a(i, 1)) >= fDate And DateValue(a(i, 1)) <= tDate)) Then
k = k + 1
For j = 1 To 7
If j = 1 Then 'Change the 1 in this line for the column number where you have the date
b(k, j) = Format(a(i, j), "dd/mm/yyyy")
Else
b(k, j) = a(i, j)
If j = 5 Then MyTotal = MyTotal + a(i, j)
** 'If j = 6 Then MyPrAv = MyPrAv + a(i, j) ' do nothing for this**
If j = 7 Then MyTotal1 = MyTotal1 + a(i, j)
End If
Next
End If
Next
If k > 0 Then ListBox1.List = b
With ListBox1
.ColumnWidths = "70;100;90;150;80"
For i = 0 To .ListCount - 1
.List(i, 4) = Format(.List(i, 4), "#,##0.00")
.List(i, 5) = Format(.List(i, 5), "#,##0.00")
.List(i, 6) = Format(.List(i, 6), "#,##0.00")
Next i
End With
TextBox4.Text = Format(MyTotal, "#,##0.00")
TextBox5.Text = Format(MyTotal1, "#,##0.00")
**' TextBox6.Text = Format(Application.Average(MyPrAv), "#,##0.00") ' replace with line below**
**TextBox6.Text = Format(MyTotal1 / MyTotal, "#,##0.00") ' divide summed price by summed quantity**
End Sub

I tried this for item 1200R20 G580 JAP and it gave the correct avearge of 2,484.70 with no data in textboxes 2 and 3 (FD and TD).

**Revision #2 (17 June 2024)**

Given the user clarified the requirement is the average BATCH price (not the true avarage *unit* price), the SECOND attached file has the following code (changes in bold):

```
Sub FilterData()
Dim lindex&
Dim MyTotal As Double, MyTotal1 As Double,
```**MyPrAv As Double**
Dim fDate As Date, tDate As Date
Dim txt1 As String, txt2 As String, txt3 As String
Dim i As Long, j As Long, k As Long, iMax As Long
Dim p As Long
ListBox1.Clear: MyTotal = 0
On Error Resume Next
fDate = DateValue(TextBox2.Text)
tDate = DateValue(TextBox3.Text)
On Error GoTo 0
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
**p = 0 ' reset batch counter**
For i = 1 To UBound(a)
If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
If (LCase(a(i, 4)) Like LCase(txt1) & "*") And ((tDate = 0 And fDate = 0) Or (DateValue(a(i, 1)) >= fDate And DateValue(a(i, 1)) <= tDate)) Then
k = k + 1
For j = 1 To 7
If j = 1 Then 'Change the 1 in this line for the column number where you have the date
b(k, j) = Format(a(i, j), "dd/mm/yyyy")
Else
b(k, j) = a(i, j)
If j = 5 Then MyTotal = MyTotal + a(i, j)
If j = 6 **Then**
MyPrAv = MyPrAv + a(i, j) **' add batch prices**
** p = p + 1 ' count batches**
** End If**
If j = 7 Then MyTotal1 = MyTotal1 + a(i, j)
End If
Next
End If
Next
If k > 0 Then ListBox1.List = b
With ListBox1
.ColumnWidths = "70;100;90;150;80"
For i = 0 To .ListCount - 1
.List(i, 4) = Format(.List(i, 4), "#,##0.00")
.List(i, 5) = Format(.List(i, 5), "#,##0.00")
.List(i, 6) = Format(.List(i, 6), "#,##0.00")
Next i
End With
TextBox4.Text = Format(MyTotal, "#,##0.00")
TextBox5.Text = Format(MyTotal1, "#,##0.00")
**If p > 0 Then**
TextBox6.Text = Format(**MyPrAv / p**, "#,##0.00") ' calculate average batch price
** Else**
** TextBox6.Text = ""**
**End If**
End Sub