Forum Discussion
How do we section dynamic arrays for running averages, running slopes or other functions?
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#)
isaacmooreuky The following formula will spill the averages. Don't believe there are "spillable" alternatives for MEDIAN and STDEV.
=AVERAGEIF(N10#,P10#,O10#)
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.
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.
PeterBartholomew1 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!
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.