Jennifer, I wanted to follow up with a couple of things, and offer a nuanced and expanded explanation to Rory's comment. First, the "ridx" that appears in some of the options I posted previously is a named variable used within the LET function. LET allows us to define a variable name followed by the expression for that variable. We might choose to establish this named variable for the same reason we might use Excel's Name Manager for establishing a named variable: for convenience in those cases where a value is used subsequently, perhaps multiple times, and we don't want to repeat the cumbersome expression for it. Another reason for doing so is to compute intermediate results that are needed for a final result, and rather than placing those intermediate results in helper cells on the worksheet, we can assign them to the named variables for later use in the LET function...so LET can essentially be used to wrap a series of computations into a single expression, and only the last expression in LET will be reported out as the result.
In the expressions where I defined "ridx", ridx represents the row index for whichever Value in the table happens to be the targeted one. For example, 5 appears in the 8th row index position of the [Value] column. That happens to be found on row 14 of my worksheet...and the #Headers for Table2 happen to be located on row 6 of my worksheet. Therefore, when the formula for computing the "Sum of Last N" is pulled down to this particular row (14), the ridx expression, which is ROW()-ROW(Table2[#Headers]), will be computed as 14 - 6, which is 8...meaning the value of 5 is found in the 8th row of the Value column. This is used to determine whether the arrays to be summed or averaged should use the last "N" values (when ridx >= N) or the last "ridx" values (when ridx < N).
The LAMBDA-based formulas I offered assumed the results were to be delivered somewhere outside the table, and the use of SEQUENCE inside the MAP LAMBDA helper function will cause all of the results to "spill" from a single formula (i.e., the MAP formula is entered in one cell only, and the Sum of Last N for all values in the [Value] column of the table will be delivered as a spilling array). To Rory's point, this works outside of the table, but not inside the table, because official Excel tables have a limitation in that spilling results are not accommodated. This behavior is almost by necessity, because a formula entered into the top cell of a table is automatically applied to all cells below it, and this would conflict with spilling results. You can see this problem appearing as a series of #SPILL! errors in the [Sum of Last N_a] column, which I suspect is similar to what you observed. But if we understand the behavior, we can still see these results: consider what happens when we wrap the formula with a TEXTJOIN function to cause all of the spilling results to be combined into a single cell (see the [Sum of Last N_b] column). Then we'll see that the single formula initially entered into the topmost cell is delivering all desired results, but Excel's "table" behavior causes this same formula to be repeated for every cell in the column. Importantly, this illustrates that LAMBDA functions can be used inside official tables, but they need to be used in such a manner to deliver only a single result, and not to "spill" multiple results.
In this case, as I mentioned above, it is the SEQUENCE function that causes multiple results to be spilled by the LAMBDA function. We can revise the Sum and Average formulas to use the row index for each value (rather than an array of row indexes), knowing that the row index will be computed as the formula is automatically copied down the column due to Excel's table behavior. In these revised formulas (columns D and E), I've used the same expression for "ridx" as used in other, earlier formula variants. But it's really not necessary to go to this trouble inside the table...in fact, it is rather silly to pass a single value to the MAP function (rather than an array). If we're going to rely on Excel's official table formula-copying-down behavior to automatically generate the correct ridx value, we can use the non-LAMBDA versions of the formulas inside the table, as shown in columns H and I. Bottom line: if you want the results inside the table, I would take an approach like that shown in cols H and I and rely on the table behavior to deliver all of the results.
Note that two of the formula refer the the "Num" named variable. You don't really need to do that if you would prefer to simply refer to the fixed cell $C$5, which contains the value of "N".
| MrExcel_20240609.xlsx |
|---|
|
|---|
| C | D | E | F | G | H | I |
|---|
| 5 | 5 | <-- N | | | | | |
|---|
| 6 | Value | Sum of Last N | Average of Last N | Sum Last N_a | Sum Last N_b | Non-LAMBDA Sum of Last N | Non-LAMBDA Average of Last N2 |
|---|
| 7 | 3 | 3.00 | 3.00 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 3.00 | 3.00 |
|---|
| 8 | 6 | 9.00 | 4.50 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 9.00 | 4.50 |
|---|
| 9 | 1 | 10.00 | 3.33 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 10.00 | 3.33 |
|---|
| 10 | 7 | 17.00 | 4.25 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 17.00 | 4.25 |
|---|
| 11 | 3 | 20.00 | 4.00 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 20.00 | 4.00 |
|---|
| 12 | 1 | 18.00 | 3.60 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 18.00 | 3.60 |
|---|
| 13 | 2 | 14.00 | 2.80 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 14.00 | 2.80 |
|---|
| 14 | 5 | 18.00 | 3.60 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 18.00 | 3.60 |
|---|
| 15 | 3 | 14.00 | 2.80 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 14.00 | 2.80 |
|---|
| 16 | 8 | 19.00 | 3.80 | #SPILL! | 3, 9, 10, 17, 20, 18, 14, 18, 14, 19 | 19.00 | 3.80 |
|---|
|
|---|
