How to Predict When Cumulative Excel Values Reach a Target in WPS Office Spreadsheets
Question details
Use cumulative totals and forecast formulas in WPS Spreadsheets to estimate the date or day when daily electricity meter readings reach a 1,000 kWh target.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Daily electricity meter readings are recorded in a spreadsheet, and the predicted date or day must be used in another table.
- Observed behavior
- The workbook contains the raw daily readings, but the user needs a formula-driven way to identify when the cumulative total will reach or exceed 1,000 kWh.
Organize your worksheet so column A contains dates or day numbers, column B contains the daily meter readings, and column C is available for cumulative totals. Confirm that the values in column A are valid dates or numeric day numbers before using the forecast formula.
Use Cumulative Totals and FORECAST to Estimate the Target Date
Best when you have daily dates or day numbers in column A and meter readings in column B, and you want a reusable formula that updates as new readings are added.
This method creates a running total in column C and applies the FORECAST function using known day or date values and cumulative totals to estimate when cumulative consumption crosses 1,000.
Select cell C2, type =IF(B2="","",SUM($B$2:B2)), and press Enter. This returns the cumulative consumption up to the current row and keeps the cell blank when the reading is empty.
Drag the fill handle from C2 down through all rows that contain meter readings to create a running total for each date or day.
Select cell D3, type =IF(B3="","",INT(FORECAST(1000,$A$2:$A3,$C$2:$C3))), and press Enter. This estimates the date or day value at which the cumulative total reaches 1,000.
Fill the formula down column D alongside your data. If column A contains dates, format column D as Date to display the predicted date instead of a serial number.
Estimate the Target Day with LINEST and LET
Use this if you prefer a single formula that estimates the day number directly from daily readings without adding a cumulative helper column.
Create a Cumulative Target Forecast in WPS Spreadsheets
Use WPS Spreadsheets to build a live cumulative usage table and forecast the date or day when your meter readings cross 1,000 kWh. The same formulas recalculate automatically when new readings are added.
- 1. Open WPS Spreadsheets: Launch WPS Office and open your existing Excel workbook, or create a new spreadsheet for your daily meter readings.
- 2. Arrange the data columns: Place dates or day numbers in column A and daily readings in column B, then use column C for cumulative totals.
- 3. Apply the cumulative formula: In cell C2 enter =IF(B2="","",SUM($B$2:B2)) and copy it down to all reading rows.
- 4. Add the forecast formula: In cell D3 enter =IF(B3="","",INT(FORECAST(1000,$A$2:$A3,$C$2:$C3))) and copy it down. Format column D as Date if column A uses dates.
Frequently Asked Questions
How do I format the predicted result as a date in WPS Spreadsheets?
Select the forecast cells, press Ctrl+1 or right-click and choose Format Cells, then click Date, choose a date format, and click OK. If the result is a day number instead of a date, add the result to the start date in a separate cell.
Can I use the formula when the target is not 1,000 kWh?
Yes. Replace 1000 in the FORECAST formula with your target value. For example, use =IF(B3="","",INT(FORECAST(500,$A$2:$A3,$C$2:$C3))) to estimate when cumulative usage reaches 500 kWh.
Why does the FORECAST formula start in D3 instead of D2?
FORECAST needs at least two known cumulative points to estimate the trend. Starting in D3 helps ensure column C has enough cumulative data for the formula to calculate properly.
Will the forecast update when I add new meter reading rows?
Yes. If you copy the cumulative and forecast formulas down into new rows, or use a WPS table, the totals and forecast update automatically as additional daily readings are entered.
