More Help with Unique Values

[David Lipetz]

I have a very large table filled with transactional data. Each row is one
transaction. Each transaction has a Master Customer number, a Sub Customer
number, transaction date and transaction total.A Master Customer has
anywhere from 1 to 10 or more Sub Customer numbers. This means that if a
Master Customer had 3 Sub Customer accounts, and each had a transaction in
March, there would be 3 rows of data each labeled for March.

I am trying to calculate the average total monthly transaction size by
Master Customer number. I need to use a formula rather than a Filter or
Pivot Table.

Using the AVERAGE function will not work for Master Accounts with more than
1 Sub Account since the number of months will be calculated incorrectly.My
next attempt was to derive the average by calculating the TOTAL for the
Master Account then dividing by the unique number of months.Calculating the
total for the Master Account is an easy SUMPRODUCT formula:

=SUMPRODUCT(--(MNO=A2),TOT)
where MNO is named range for Mast Customer number and TOT is named range for
invoice total

What I have been unable to figure out is how to calculate the unique number
of months for a given Master Customer Number.

A formula like the one below could work if I could figure out how to embed a
SUMPRODUCT statement to narrow the range just to the Master Customer number.

=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))

Is there a way to replace the A2:A10 references above with something like
SUMPRODUCT(--(MNO=A2),PERIOD) where MNO is named range for Master Customer
number and PERIOD is named range for transaction month (MM/YY)?

I tried this and it does not work - the result is always 1
=SUM(IF(FREQUENCY(SUMPRODUCT(--(MNO=A2),PERIOD),SUMPRODUCT(--(MNO=A2),PERIOD))>0,1))

Ideas?

 

[Bernie Deitrick]

You are trying to get an array of arrays using another array to work as a
single formula. Much easier to do that in VBA with a UDF, used like

=MCAVerage($A$3:$A$1000,$B$3:$B$1000,$C$3:$C$1000,D3)

Where A has the master Account number, B has the transaction Date, C has the
transaction value, and D3 has the Master Accounf of interest.

Function MCAverage(MC As Range, _
TransDate As Range, _
TransValue As Range, _
C As Range) As Double

Dim myMonths() As Date 'Array to hold months with transactions
Dim myC As Range 'Range object to step through ranges
Dim TotalValue As Double
Dim tempDate As Date
Dim trsDate As Date
Dim MCount As Integer
Dim i As Integer

MCount = (Application.Max(TransDate) - Application.Min(TransDate)) \ 30
+ 3
ReDim myMonths(1 To MCount)
MsgBox MCount

TotalValue = 0
MCount = 0

For Each myC In MC
If myC.Value = C.Value Then 'Master Customers match
TotalValue = TotalValue + Intersect(TransValue,
myC.EntireRow).Value
trsDate = Intersect(TransDate, myC.EntireRow).Value
tempDate = DateSerial(Year(trsDate), Month(trsDate), 1)
For i = 1 To UBound(myMonths)
If myMonths(i) = 0 Then
myMonths(i) = tempDate
MCount = MCount + 1
End If
If myMonths(i) = tempDate Then GoTo Matched
Next i
Matched:
End If
Next myC

MCAverage = TotalValue / MCount
End Function



HTH,
Bernie
MS Excel MVP