I am trying to calculate the Customer Lifetime Value. I have all the components except for the Average Customer Lifespan. I have the formula (see below), which works on Excel 365 but not on Excel 2021 (Mac version) because it uses the BYROW and LAMBDA function, which this version does not support.
Here is the formula, which is working only on the 365 version:
=LET(
c, UNIQUE(Purchase[Customer Name]),
dys, IFERROR(
BYROW(
c,
LAMBDA(arr,
LET(
f, FILTER(
Purchase[Purchase Date],
(Purchase[Customer Name] = arr) * (Purchase[Unit No.] = 62)
),
MAX(f) - MIN(f)
)
)
),
""
),
avg, AVERAGE(dys),avg
)
Can you please help me converting this formula into a VBA code that I could use on an Excel 2021 (Mac)? (using column and table names).
My data in excel have all the historical purchases made by all customers in a named table. A purchase creates multiple rows for each item purchased in that order. Same customers can have multiple purchases. Attached is an example of data that I have simplified.
Column Purchase[Customer Name] has the Customer name (unique for each customer), Column Purchase[Purchase Date] has the date of each purchase/order and Column Purchase[Unit No.] has the Unit No.
What I need is: for each individual customer to calculate the date difference between his last and first purchase and then average all of the differences. At the same time, I need this to be calculated only if the Unit No. in Column Purchase[Unit No.] matches "62". (Therefore, the calculation is not done for customer named "C" in the table.)
I'd like have the result in one cell (if possible). The result for the data provided is "11" (13 for customer "A" and 9 for customer "B").
Customer Name | Purchase Date | Unit No. |
---|---|---|
A | 1/1/23 | 62 |
A | 1/1/23 | 62 |
A | 1/1/23 | 62 |
A | 1/1/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
B | 1/2/23 | 62 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
C | 1/3/23 | 0 |
A | 1/5/23 | 62 |
A | 1/5/23 | 62 |
A | 1/5/23 | 62 |
A | 1/5/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
B | 1/8/23 | 62 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
C | 1/9/23 | 0 |
A | 1/11/23 | 62 |
A | 1/11/23 | 62 |
A | 1/11/23 | 62 |
A | 1/11/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
B | 1/14/23 | 62 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
C | 1/15/23 | 0 |
Thank you
I was expecting the Excel 2021 to have all the necessary formulas, which it does not.
Option 1
=LET(
custname,UNIQUE(FILTER(Purchase[Customer Name],Purchase[Unit No.]=62)),
maxdate,MAXIFS(Purchase[Purchase Date],Purchase[Customer Name],custname),
mindate,MINIFS(Purchase[Purchase Date],Purchase[Customer Name],custname),
AVERAGE(maxdate-mindate)
)
Option 2
let
Source = Excel.CurrentWorkbook(){[Name="Purchase"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Purchase Date", type datetime}, {"Unit No.", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Unit No."] = 62)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Customer Name"}, {{"MinDate", each List.Min([Purchase Date]), type nullable datetime}, {"MaxDate", each List.Max([Purchase Date]), type nullable datetime}}),
#"Added Custom" = Table.TransformColumnTypes(Table.AddColumn(#"Grouped Rows", "Span", each [MaxDate]-[MinDate]),{{"Span", type number}}),
#"Calculated Average" = List.Average(#"Added Custom"[Span])
in
#"Calculated Average"