Forum Discussion

CycleWyze's avatar
CycleWyze
Copper Contributor
Sep 08, 2024

I cant seem to figure out this formula

Im trying to get the average time between customer orders.

 

I was thinking of using a combination of AVERAGE, DATEDIF and the next formula is where im struggling.

I have tried XLOOKUP but this only returns one value. Ive also tried INDEX but cant seem to get it to work. Now im stumped

 

An example of what im trying to do here:

 

don9/7/2024
randy9/9/2024
john9/10/2024
albert9/12/2024
john doe9/13/2024
jane doe9/14/2024
randy9/15/2024
anthony9/16/2024
james9/17/2024
randy

9/18/2024

 

I want to first off find all the dates associated with "Randy" and then I want to find the time between each date. I can figure those parts out just fine but how do I now get the average between these dates?

 

I think my struggle is that I dont have a place to display each customers entire purchase list so im trying to do it all within one formula. 

Formulas and Functions
  • mathetes's avatar
    mathetes
    Silver Contributor
    Sep 08, 2024

    CycleWyze 

     

    You seem to be a person who likes to figure things out for yourself. So let me point you to 

    what are called the "dynamic array" functions. I think you'll find what you're looking for here. Start, maybe, with FILTER

     

    By all means, though, come back here if you still come to a dead end. And perhaps post a copy of the worksheet you're using, so we don't have to re-create something ourselves.

    • CycleWyze's avatar
      CycleWyze
      Copper Contributor
      Sep 08, 2024
      i will check this out now, however I cant provide the actual worksheet as is has sesitive information on them.

      Thanks for the info
      • mathetes's avatar
        mathetes
        Silver Contributor
        Sep 08, 2024

        CycleWyze 

        i will check this out now, however I cant provide the actual worksheet as is has sesitive information on them.

         

        You could create a mockup that removes all sensitive info.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 08, 2024

    CycleWyze 

    If you have access to the newly released GROUPBY function, it can make short work of this request.

    =LET(
    avg_days, LAMBDA(arr,
        LET(
            k, ROWS(arr),
            sorted, SORT(arr),
            IF(k = 1, @arr, AVERAGE(DROP(sorted, 1) - DROP(sorted, -1)))
        )
    ),
    GROUPBY(Table1[Name], Table1[Date], avg_days)
)

    First, 'avg_days' is created to be used with GROUPBY.  This function will take the date(s) being returned by GROUPBY and do some subtraction.  If only 1 date is found for a person, then the date is returned instead of the average. Sorting is done to ensure the dates being subtracted are in ascending order.

     

    GROUPBY obtains a list of unique names from the table and takes in the date as the value -- the avg. days Lambda is applied to each return.

     

     

Resources