0

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:

  1. 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").
  2. 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.

P E
  • 165
  • 11

1 Answers1

0

OK, I've figured out a solution. For whatever reason, it seems you can't use ROW() with named dynamic array formulas, but it turns out I don't need to. I can simply use: =MMULT((--(twelve>=TRANSPOSE(twelve))),FTE) and I'll get the desired result (with 75000 being the value in B3):

6369.86
12123.29
18493.15
24657.53
31027.40
37191.78
43561.64
49931.51
56095.89
62465.75
68630.14
75000.00

I also found it useful to assign the above formula to a name, so I can conveniently enter it into any cell on the sheet and get the same output.

One more thing: I realised I can define the named formula twelve more simply by using =SEQUENCE(12), though the OFFSET approach still works. (Still getting used to some of there 'new' Excel formulas--not so new anymore, I guess.) I edited original question to mention this.

P E
  • 165
  • 11