Excel 365 Formula to Find the Date a Cumulative Total Reaches 100
Question details
The user needs to return the first date on which accumulated values from a selected start date reach or exceed 100.
- Product
- WPS Spreadsheets or Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- The source data contains Date and Value columns, and the calculation should begin at a specified start date.
- Observed behavior
- The desired result is the first date whose running total is at least 100.
Place the source records in a table with Date and Value columns, and make sure the dates are sorted from earliest to latest. Confirm that DateInput contains a date found in Table1[Date].
Use a dynamic-array formula to return the first date at or above 100
Use this solution when your spreadsheet supports LET, DROP, SCAN, XMATCH, and XLOOKUP and your source data is stored in Table1.
The formula removes records before DateInput, calculates a running total with SCAN, and uses XLOOKUP to return the first date where the cumulative value reaches or exceeds 100. The match mode 1 searches for an exact match or the next larger cumulative value.
Select the Date and Value columns, choose Insert > Table, and name the table Table1. Ensure the table headers are exactly Date and Value.
Enter the starting date in a cell such as C1 and define or reference it as DateInput. The date must match one of the entries in Table1[Date].
Select the result cell and enter: =LET(discard,XMATCH(DateInput,Table1[Date])-1,Values,DROP(Table1[Value],discard),Dates,DROP(Table1[Date],discard),Accumulate,LAMBDA(a,v,a+v),AccArray,SCAN(0,Values,Accumulate),XLOOKUP(100,AccArray,Dates,"Not reached",1))
Open Home > Number Format, select Date, and verify that the returned date is the first row after DateInput where the running total is 100 or greater.
Use a cumulative helper column for broader compatibility
Use this approach when dynamic-array functions are unavailable or when you want to inspect each running-total value row by row.
Calculate the First Date a Running Total Reaches a Target in WPS Spreadsheets
WPS Spreadsheets can organize the records in an Excel-compatible table, calculate cumulative values with dynamic-array formulas, and return the first date that reaches or exceeds a target such as 100.
- 1. Open or create the workbook: Launch WPS Office, open the workbook in WPS Spreadsheets, and select the worksheet containing the Date and Value data.
- 2. Convert the data to a table: Select the data range, choose Insert > Table, and name the table Table1 with Date and Value headers.
- 3. Set the starting date: Enter the required start date in a cell such as C1 and use that cell as DateInput in the cumulative-total formula.
- 4. Calculate the threshold date: Select an empty result cell and enter the LET, DROP, SCAN, and XLOOKUP formula to return the first date when the running total reaches 100.
Frequently Asked Questions
What does the formula return if the cumulative total never reaches 100?
It returns "Not reached" because that text is supplied as the XLOOKUP not-found result. You can replace it with another message or an empty string.
How do I change the target from 100 to another number?
Replace both occurrences of 100 in the formula's XLOOKUP section with the required target, such as 250.
Can I start the calculation from a date that is not the first record?
Yes. Set DateInput to a date in Table1[Date]. XMATCH and DROP remove earlier rows before SCAN calculates the running total.
Why might the formula return the wrong date?
Check that the Date and Value columns are aligned, dates are sorted in ascending order, DateInput exactly matches a table date, and values are numeric rather than text.
