Forum Discussion
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 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.
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
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#)
16 Replies
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.
- 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.
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#)
- 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")).
