Forum Discussion

isaacmooreuky's avatar
isaacmooreuky
Copper Contributor
Jul 18, 2021
Solved

How do we section dynamic arrays for running averages, running slopes or other functions?

Hey All! I am about 3 weeks into intensive use of the dynamic array formulas in Excel, and I keep running into types of problems where a formula won't spill. The recourse is to drag the formula acros...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Jul 18, 2021

    isaacmooreuky 

    I have attached a couple of variations that may be of interest to you.

    = LET(
  SpeedEcon, FILTER(Economy#,Speed#=@DistinctSpeed#),
  mean,      AVERAGE(SpeedEcon),
  median,    MEDIAN(SpeedEcon),
  stddev,    STDEV.S(SpeedEcon),
  count,     COUNT(SpeedEcon),
  CHOOSE({1,2,3,4}, mean, median, stddev, count) )

    The first (green) performs the computationally intensive FILTER operation only once for each speed and outputs multiple statistical measures as an array.

    The second (orange)  is closer to providing the spilt ranges you were hoping to achieve, but median and standard deviation might require helper ranges.

    = AVERAGEIFS(Economy#, Speed#, DistinctSpeed#)

= COUNTIFS(Speed#, DistinctSpeed#)

     

Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Jul 18, 2021
My first reaction is that your formula needs Rage of data and it should =AVERAGE(FILTER(A2:C15, B2:B15=F2, "No Data")).
  • isaacmooreuky's avatar
    isaacmooreuky
    Copper Contributor
    Jul 18, 2021
    Yeah, but then it wouldn’t be a dynamic array anymore.
    • Rajesh_Sinha's avatar
      Rajesh_Sinha
      Iron Contributor
      Jul 19, 2021
      FILTER is the Dynamic array,, and this sort of combination doesn't makes any adverse effect on it's performance !!
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Jul 18, 2021

      isaacmooreuky The following formula will spill the averages. Don't believe there are "spillable" alternatives for MEDIAN and STDEV.

      =AVERAGEIF(N10#,P10#,O10#)

       

      • RecalcOrDie's avatar
        RecalcOrDie
        Iron Contributor
        Oct 19, 2021

        Greetings Riny_van_Eekelen 
        I asked this question to Mike Girvin from ExcelisFun YouTube channel and he replied that it´s possible with BYROW and LAMBDA functions, so all credits to him please.  Here´s his response:

        Aggregate functions like SUM and MEDIAN can never spill. But with BYROWS and LABMDA it can be done!

        Data set in range B3:C14:

        Product Values
        Quad 26
        Aspen 96
        Carlota 60
        Quad 40
        Quad 30
        Aspen 72
        Carlota 11
        Carlota 45
        Carlota 97
        Aspen 96
        Aspen 79
        Quad 69

        Formula with short variables: =LET(p,B3:B14,v,C3:C14,up,SORT(UNIQUE(B3:B14)),mv,BYROW(up,LAMBDA(m,MEDIAN(FILTER(C3:C14,B3:B14=m)))),CHOOSE({1,2},up,mv))

        Formula with descriptive variables: =LET(Products,B3:B14,Numbers,C3:C14,SortUniqueProducts,SORT(UNIQUE(B3:B14)),SpilledMedians,BYROW(SortUniqueProducts,LAMBDA(ProductCriteria,MEDIAN(FILTER(C3:C14,B3:B14=ProductCriteria)))),CHOOSE({1,2},SortUniqueProducts,SpilledMedians))

        Bam! Boom! Go Team!!!!

        I am attaching the file he shared. Hope it helps.

Resources