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#)
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.
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?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.