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#)
- FILTER is the Dynamic array,, and this sort of combination doesn't makes any adverse effect on it's performance !!
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.
- 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?
