How to Create a Spilled Excel Formula for Expenses by Date and Interval
Question details
The user needs to generate a single spilled-array formula to flag recurring expenses across specific reporting periods based on their start dates, end dates, and payment intervals.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Financial tracking or budgeting where recurring expenses, such as monthly or quarterly payments, need to be mapped to specific time periods automatically without dragging formulas.
- Observed behavior
- The goal is to output a dynamic spilled array that calculates and displays whether an expense falls into respective reporting columns across all rows simultaneously.
Ensure your spreadsheet software supports dynamic arrays and modern functions like LET, SEQUENCE, MAP, and LAMBDA, which are typically available in Microsoft 365, Excel 2021, and the latest versions of WPS Office.
Use LET, SEQUENCE, and MAP for a Dynamic Spilled Array
This method defines variables within a single formula and maps the calculation across all expense rows, generating a complete spilled array for the entire reporting grid.
By combining LET to define your ranges (start dates, end dates, intervals) and MAP to iterate through each row, you can generate a dynamic array that automatically spills the results. SEQUENCE and EDATE calculate the exact payment dates for each expense to check against the reporting periods.
Click on the top-left cell of your reporting grid where you want the array results to begin spilling.
Enter the LET function to assign names to your ranges. For example: =LET(periodStart, G2:R2, periodEnd, G3:R3, interval, B5:B12, startDate, C5:C12, endDate, D5:D12, ...)
Within the LET function, calculate the total number of payments using standard date math: duration, ((YEAR(endDate)-YEAR(startDate))*12+MONTH(endDate)-MONTH(startDate)+1)/interval.
Use MAP and LAMBDA to iterate through the dates. Use EDATE and SEQUENCE to generate the payment arrays, and multiply Boolean logic ((payment>=periodStart)*(payment<periodEnd)) to flag the expenses.
Press Enter to execute the formula. The results will automatically spill across the designated rows and columns.
Define a Custom LAMBDA Function for Payment Dates
Create a reusable LAMBDA function in the Name Manager to calculate payment dates for a single expense, then apply it to your dataset using MAP.
Handle Dynamic Arrays and Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like LET, SEQUENCE, and MAP, allowing you to build powerful financial models and expense trackers with ease.
- 1. Install WPS Office: Download and install WPS Office from the official website and launch WPS Spreadsheet.
- 2. Open your financial workbook: Open your existing expense tracking or budgeting workbook within the application.
- 3. Input the dynamic array formula: Select the target cell in your reporting grid and input your dynamic array formula utilizing LET, SEQUENCE, and MAP.
- 4. Generate the spilled results: Press Enter to instantly execute the calculation and generate the spilled array across your reporting periods.
Frequently Asked Questions
Why am I getting a #SPILL! error when using this formula?
A #SPILL! error occurs when the dynamic array formula needs to output results into multiple cells, but one or more of the destination cells already contain data, spaces, or formatting. Clear the blocking cells to allow the formula to spill correctly.
Can I use this formula for weekly intervals instead of monthly?
Yes. To calculate weekly intervals, you will need to adjust the duration calculation and use standard date addition (e.g., adding 7 days multiplied by the sequence step) inside the SEQUENCE function instead of using EDATE, which is specifically designed for manipulating months.
Are LET and SEQUENCE functions available in older versions of Excel?
No, LET, SEQUENCE, MAP, and LAMBDA are modern dynamic array functions introduced in Microsoft 365 and Excel 2021. If you are using an older version, you will need to rely on traditional helper columns and manually drag the formulas across your grid.
How does the MAP function work in this spilled array?
The MAP function iterates over each value in the provided arrays (such as the start dates and intervals column ranges) and passes them to a LAMBDA function. This allows the formula to perform the expense date calculation row by row and spill the final evaluated results automatically into a single grid.
