Formula for Average Customer Lifespan in excel for Customer Lifetime Value

Question

I am trying to calculate the Customer Lifetime Value. I have all the components except for the Average Customer Lifespan, which I am having trouble compiling into a formula.

My data in excel have all the historical purchases made by all customers. A purchase creates multiple rows for each item purchased in that order (not important here). Attached is an example of data that I have simplified.

Column B has the Customer name (unique for each customer), Column C has the date of the purchase/order and Column D has the Unit No.

What I need it for each individual customer to calculate the date difference between his first and last purchase (in this case the number of days). (The cell H2 and H3 just shows the steps in between for better understanding). The Cell H4 is then the average of the numbers in H2 and H3.

At the same time, I need this to be calculated only if the Unit No. in Column D matches "62". (Therefore the calculation is not done for customer "C".)

I'd like have the whole formula in one cell, that would produce the result seen in cell H4. Can you please help me with that?

Thank you

screenshot of excel

I tried ChatGPT, which didn't produce the expected result.

Answer 1

Assuming no version constraints, as per your tags, the following formula, entered into a single cell, will produce the output:

=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),
    tbl, VSTACK(
        {"Customer Name", "Customer Lifespan"},
        HSTACK(c, dys),
        HSTACK("Average Lifespan", avg)
    ),
    FILTER(tbl, CHOOSECOLS(tbl, 2) <> "")
)

Output from your posted table

Edit: Individual formulas avoiding BYROW and LAMBDA+

N3: =SORT(
    UNIQUE(
        FILTER(
            Purchase[Customer Name],
            Purchase[Unit No] = 62
        )
    )
)

O3: =MAXIFS(
    Purchase[Purchase Date],
    Purchase[Customer Name], N3,
    Purchase[Unit No], 62
) -
    MINIFS(
        Purchase[Purchase Date],
        Purchase[Customer Name], N3,
        Purchase[Unit No], 62
    )

O4: =MAXIFS(
    Purchase[Purchase Date],
    Purchase[Customer Name], N4,
    Purchase[Unit No], 62
) -
    MINIFS(
        Purchase[Purchase Date],
        Purchase[Customer Name], N4,
        Purchase[Unit No], 62
    )

O6: =AVERAGE(O3:O5)

Results from Formulas

Answer 2

You can use this formula:

=LET(d,B2:D14,
uCustomer,UNIQUE(FILTER(INDEX(d,,1),INDEX(d,,3)=62)),
uDelta,BYROW(uCustomer,LAMBDA(c,MAXIFS(INDEX(d,,2),INDEX(d,,1),c)-MINIFS(INDEX(d,,2),INDEX(d,,1),c))),
VSTACK(HSTACK(uCustomer,uDelta),HSTACK("average",AVERAGE(uDelta))))

It first retrieves the unique customers with UnitNo 62.

Then calculates per unique customer the delat

Finally all information is put together by HSTACK and VSTACK