What dynamic array formula can I use to calculate runway or burnrate in Excel?

Question

I'm using dynamic array functions in Excel (SCAN, MAP, LET, BYCOL, etc); wihtout VBA or regular SUMIF formulas, to create a runway or burnrate-type table. So, I start with a $10,000 budget, month 1 $2,000 are spent, so $2,000 come out of the budget, month 2 $3,000, and so on until the cash available is 0 for the remaining months of the year. With a table showing how much cash was used from the budget per month, Desired outcome in the case below.

	A	B	C	D	E	F
1	Budget	$10,000
2	Month	1	2	3	4	5
3	Expense	-$2,000	-$3,000	-$7,000	-$4,000	-$2,000
4	Desired outcome	-$2,000	-$3,000	-$5,000	$0	$0

Note that the Desired outcome amount is how much of the budget was used to cover the expenses.

Notice that Month 3 I spent $7,000, but from the budget, only $5,000 were left; so that's what I show.

Studied all the dynamic array (SPILL!) functions and lambda functions that I could find on the internet (this video by excelisfun is great) but I couldn't make it work. Some combination between SCAN or MAP would be the go-to solution I would think.

The solution should be one formula that leverage MS365 dynamic array functions.

Answer 1

I used REDUCE to get your result:

=LET(budget,  -B1,
     expenses, B3:F3,
DROP(REDUCE(   0,    expenses, 
     LAMBDA(   x,    y,
HSTACK(        x,
               IF( SUM(x,y)>=budget,
                   y,
                   budget-SUM(x))))),
     ,1))

It creates a cumulative sum of expenses and checks if it's greater than or equal to the budget (since your expenses are negatives I converted budget to a negative).

If it is it, the value of expenses is shown, otherwise the remainder of the budget minus the cumulative sum of the expenses.