Jul 17 2021 09:34 PM - edited Jul 17 2021 10:26 PM
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
Jul 17 2021 10:24 PM
Jul 17 2021 10:30 PM
Jul 17 2021 10:30 PM
Jul 17 2021 10:34 PM
Jul 17 2021 11:28 PM
@isaacmooreuky The following formula will spill the averages. Don't believe there are "spillable" alternatives for MEDIAN and STDEV.
=AVERAGEIF(N10#,P10#,O10#)
Jul 18 2021 12:25 AM
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.
Jul 18 2021 04:29 AM
Jul 18 2021 04:32 AM
Jul 18 2021 01:33 PM
SolutionI 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#)
Jul 18 2021 03:22 PM
Jul 18 2021 09:31 PM
Jul 19 2021 02:44 AM
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.
Oct 19 2021 12:21 PM
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.
Oct 19 2021 12:55 PM
@Microsoft_Excel_Recalc_Or_Die 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.
Oct 20 2021 09:56 AM
@Microsoft_Excel_Recalc_Or_Die
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.
Oct 27 2021 09:06 PM
@Peter Bartholomew 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!