Forum Discussion

isaacmooreuky's avatar
isaacmooreuky
Copper Contributor
Jul 17, 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#)

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 18, 2021

isaacmooreuky 

You only have two array dimensions and most functions do not like 'arrays of arrays'.  Setting a long array to instances of a short array does not work except for functions like SUMIFS, but not FILTER.  Sometimes it is possible to use a formula in a Table to propagate a single value result down the column.

 

More will be possible when LAMBDA functions come into mainstream use.