I want to create a named dynamic array formula that will give me a spill showing a cumulative (running) total of another array. I want one that I can enter into any cell in my workbook and get the same results.
I have the following named formulas:
- twelve:
=ROW(OFFSET('CSM'!$A$1,0,0,12,1))[EDIT: I realised later it's simpler just to define it as=SEQUENCE(12)] This generates an array of whole numbers from 1 to 12; I use it for a number of purposes, such as producing a list of months (=TEXT(DATE(2023,twelve,1),"mmm-yy"). - FTE:
='CSM'!$B$3/12*(--(twelve=twelve))This shows FTE's monthly salary (with cell B3 on sheet CSM providing the annual salary) over 12 months.
Now, if I enter =FTE on any cell, I'll get a 12-cell spill that shows the FTE's salary for each month. That's fine, but what I really need is a named formula that when it spills will show the FTE's cumulative salary (a running total).
I can do this with a formula that links to a specific cell...for example, if I want my display to start in row 2 I use:
=SUM((--(twelve<=ROW()-1))*FTE), and then drag the formula down the remaining 11 rows. But I have to adjust this formula if I use it somewhere else--for instance, if I want to start my spill on row 10 I have to write =SUM((--(twelve<=ROW()-9))*FTE). I really want a named formula that I can use anywhere on the sheet without modification, that will give me a spill showing cumulative monthly salary.
I see you can do this using the MMULT function when the data are all on a worksheet (see here), even when those data are a dynamic spilled array (see =MMULT(ROW(A1#)>=TRANSPOSE(ROW(A1#)),A1#), here). But when I use this approach on a named formula, I get a #VALUE error. I think the error actually comes from the ROW() function--it looks like I can use =ROWS(FTE) (returns 12), but not =ROW(FTE) (which returns the error).
So my question is: is there any way I can create a named formula that will give me a 12-month spill of FTE's cumulative salary by month? Maybe using SEQUENCE() or MMULT() or something else...(We haven't got Lambda yet, by the way.)
EDIT (in response to comments):
For example, if B3=$75,000, putting =FTE into any cell will produce this (spilling over into cells below):
$6,369.86
$5,753.42
$6,369.86
$6,164.38
$6,369.86
$6,164.38
$6,369.86
$6,369.86
$6,164.38
$6,369.86
$6,164.38
$6,369.86
What I want is a cumulative sum (running total) of the above; that is:
$6,369.86
$12,123.29
$18,493.15
$24,657.53
$31,027.40
$37,191.78
$43,561.64
$49,931.51
$56,095.89
$62,465.75
$68,630.14
$75,000.00
As I mentioned, we haven't got Lamda at this point, or any of those newer formulae.
=SCAN(0,FTE,LAMBDA(a,b,a+b))