Forum Discussion
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...
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#)
Yeah, but then it wouldn’t be a dynamic array anymore.
FILTER is the Dynamic array,, and this sort of combination doesn't makes any adverse effect on it's performance !!