How to Fix the WPS Spreadsheet GROWTH Function #NUM Error with Date Values
Question details
The user needs to resolve a #NUM! error returned by the GROWTH function when the known_x values are large date serial numbers, even though the same formula works with smaller ranges and all y values are positive.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A user is applying the GROWTH function to date-based data where the known_x values are Excel date serial numbers above roughly 9,209, and the function returns #NUM! even though the known_y values are positive and the ranges match.
- Observed behavior
- The GROWTH function returns a #NUM! error when known_x contains large date serial values; it succeeds without the X range or with smaller X values.
Before editing the formula, verify that the known_y and known_x ranges are the same size and contain only valid numeric values, and confirm that the date serials are real numbers rather than text. Note the minimum date serial in the known_x range, because subtracting this value is the key normalization step.
Normalize Date X Values by Subtracting the Minimum Date
Use this when GROWTH returns #NUM! with large date serials; centering the X values on the minimum date prevents the numerical limitation while preserving the growth fit.
The GROWTH function performs an exponential regression, and large X values such as date serials above 44,000 can make the internal matrix calculations unstable and produce #NUM!. Subtracting a base date shifts the X scale without changing the fitted growth rate or the relationship between X and Y.
Click the formula cell in your WPS Spreadsheet workbook and press F2 or click in the formula bar to edit it.
Change the known_x argument to your date range minus MIN(date_range), for example: =GROWTH(B2:B20,A2:A20-MIN(A2:A20)). This subtracts the earliest date from every date serial in A2:A20.
Press Enter to recalculate. Check that the #NUM! error disappears and the predicted values are numeric; apply the same normalized X expression to any related formulas.
Check Range Sizes and Use a Logarithmic Regression Alternative
Use this if the error persists after normalizing date values or if you need a transparent fallback that avoids GROWTH internal matrix limitations.
Use Normalized Date X Values to Fix GROWTH #NUM! in WPS Spreadsheet
Open your workbook in WPS Spreadsheet and edit the GROWTH function so the known_x date range is centered by subtracting MIN(date_range). WPS Spreadsheet uses familiar Excel formula syntax and recalculates the corrected regression result without changing your original date data.
- 1. Download and install WPS Office: Go to the WPS Office download page and install WPS Spreadsheet on your computer.
- 2. Open the workbook and select the GROWTH formula cell: Launch WPS Spreadsheet, open your .xlsx file, click the cell with the #NUM! error, and then click the formula bar.
- 3. Edit the known_x argument: Replace the X range with your date range minus MIN(date_range), for example A2:A20-MIN(A2:A20), and keep the known_y range unchanged.
- 4. Press Enter and confirm the result: Press Enter to recalculate. The GROWTH function should now return a numeric growth prediction instead of #NUM!.
Frequently Asked Questions
Why does the GROWTH function return #NUM! only when I use date values?
Date values are stored as large serial numbers, often above 44,000. GROWTH performs an exponential regression and can hit numerical limitations with such large X values, causing #NUM! even when the y values are positive.
How do I normalize date X values without changing my analysis?
Subtract the earliest date from every X value using date_range-MIN(date_range). This shifts the date axis while preserving the growth curve and usually resolves the #NUM! error.
Do I need Ctrl+Shift+Enter for GROWTH in WPS Spreadsheet?
In current WPS Spreadsheet versions you can normally press Enter. If you are using the legacy array formula form and need multiple results, select the output range first and press Ctrl+Shift+Enter.
What should I check if GROWTH still returns #NUM! after normalizing dates?
Verify that known_y and known_x have matching dimensions and contain only numeric values, then try a logarithmic regression with LINEST using LN(known_y) as an alternative.
Can WPS Spreadsheet open Excel workbooks that contain GROWTH formulas?
Yes. WPS Spreadsheet is compatible with Microsoft Excel .xlsx and .xls files and supports the GROWTH function, so you can edit and fix existing Excel workbooks without converting them.
