SOLVED

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

Copper Contributor

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 across the range! Is there a way to section dynamic arrays over a certain number of rows so that the output of standard functions such as SLOPE, AVERAGE, MEDIAN, SUM, or LINEST will spill? For instance, if I select values to filter by, which I have selected using UNIQUE, I cannot filter data by that array without dragging a formula to cover the entire range of unique values.

 

Here is a formula audit mode image to put this in perspective.

isaacmooreuky_0-1626582630711.png

What I want to do is use:

AVERAGE(FILTER($O$10#,$N$10#=$P10#)

and have the system run down P10# and spill out the entire selection of formulas. Is it clear what I would like to do here? This is the easier version, the harder version I would filter by a range of values and run standard functions on those outputs.

Maybe this is a hard problem, but I want to make sure it isn't a stupid problem first!

 

Thanks,

Isaac

 

16 Replies
Please share the WB if possible to test the command using the source data !
My first reaction is that your formula needs Rage of data and it should =AVERAGE(FILTER(A2:C15, B2:B15=F2, "No Data")).
Yeah, but then it wouldn’t be a dynamic array anymore.

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

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

 

@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.

Averageif spills based on the larger dynamic array. I suspect these functions would work better if there was a datatype that Excel "ranked" higher than dynamic arrays so that it could loop through that datatype first and then the dynamic array. Thoughts?
OK, That makes sense! I find myself needing to solve this particular problem quite often, so I will keep my eyes peeled for lambda for enterprise.
best response confirmed by isaacmooreuky (Copper Contributor)
Solution

@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#)

 

That is very useful! Yours is the first use of LET I have actually seen in a spreadsheet so far.
However, I guess it doesn't solve sectioning arrays in functions where the master array is not specified. AVERAGEIFS or COUNTIFS has a criteria range which it spills to, which allows the preferred spill behavior. Is there a data format or a tool available within LET or LAMBDA that would override the spill range size?
FILTER is the Dynamic array,, and this sort of combination doesn't makes any adverse effect on it's performance !!

@isaacmooreuky 

I have added further examples of LET used for the running slope calculation (purple).

= LET(
  t,   INDEX(Time,@k#):INDEX(Time,6+@k#),
  alt, INDEX(Altitude,@k#):INDEX(Altitude,6+@k#),
  climbRate, SLOPE(alt,t),
  climbRate )

 The first returns column ranges for the portion of the timeline to be analysed.  The second formula transposes the array and could, in theory, display the entire table as a 2D array.

= LET(
  t,   INDEX(Time,    {0,1,2,3,4,5,6}+@k#),
  alt, INDEX(Altitude,{0,1,2,3,4,5,6}+@k#),
  climbRate, SLOPE(alt,t),
  climbRate )

Although this makes the calculation readable and links each calculation to an index, it will not solve the problem of spilling the result.  That requires LAMBDA which is currently on beta release only.

 

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.

@RecalcOrDie Good for you! When I wrote my response (July 2021) to a question, these LAMBA helper functions were not yet available. And right now, they are only available to Insiders.

@RecalcOrDie 

Hi Carlos

I agree with your decision to forego the idea of combining the distinct products and their median values in a single formula; it serves little purpose.  I would probably have chosen the MAP function in preference to BYROW, merely because it is more specific to the task.

 

Something I have tried here, is to tidy up the worksheet formula, though at the expense of moving complexity elsewhere.  I chose to define an appropriate Lambda function MEDIANIFλ, to have the same syntax as SUMIF.  The final formula is

= MEDIANIFλ(Product, distinctProducts#, Values)

which looks simple until you examine the definition of MEDIANIFλ

= LAMBDA(critRng,critVals,valRng,
    MAP(critVals,
      LAMBDA(aCritVal,
        MEDIAN(IF(critRng=aCritVal, valRng))
      )
    )
  )

It is still possible to combine the two columns, if one so chooses

= LET(
  distinctProducts, SORT(UNIQUE(Product)),
  CHOOSE({1,2}, distinctProducts, MEDIANIFλ(Product, distinctProducts, Values) ) )

As @Riny_van_Eekelen points out, there are very few forum members in a position to try these formulas, but it may seed some ideas for the future.  Either that, or we may see the more dynamic members of the group running for the hills whilst more sedentary members might be found with sand over their head.

 

@Peter Bartholomew wow! amazing!  thank you for another great contribution to this discussion.

I am still amazed that the only ways for this calculation of "MEDIANIF" are only possible thru LAMBDA combined with MAP or BYROW.  I needed result of this MEDIANIF calculation to be in spilled range because I am working on another "Excel Graphics" (028, coming out soon!) using Dynamic Arrays functions on which with slicers, the user can either sort the boxplots, by median calculation, quartiles (1st or 3rd), IQR, min or max, either by ascending or descending order.  Sneak peak on the GIF below.
Again, thank you Peter!  

 

028 pre.gif

1 best response

Accepted Solutions
best response confirmed by isaacmooreuky (Copper Contributor)
Solution

@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#)

 

View solution in original post