How to Calculate Percentage Changes While Ignoring Blank Months in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that calculates month-to-month percentage changes while comparing each month with the most recent previous nonblank value.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Monthly data contains blank cells, and the user wants accurate month-over-month percentage changes without manually locating the last recorded value.
- Observed behavior
- The formula should skip blank months and compare the current value with the most recent previous numeric value.
Open your workbook in WPS Spreadsheets and confirm which row contains the monthly values and which row will hold the percentage-change results. Leave the first month’s result cell empty or prepare a separate results row so the formula has a previous range to search.
Use IF, LOOKUP, and IFERROR to compare each month with the most recent nonblank value
Use this formula when your data has intermittent blank months and you need percentage changes that always reference the last recorded value.
This method checks whether the current month is blank and returns an empty string instead of creating misleading 0% or -100% results. The LOOKUP part locates the last numeric value in the preceding columns, and IFERROR handles the first month where no previous value exists.
Click the cell where the first month-over-month change should appear, for example cell C3 if monthly values start in B2 and continue to the right.
Type =IF(C2="","",IFERROR((C2-LOOKUP(2,1/($B2:B2<>""),$B2:B2))/LOOKUP(2,1/($B2:B2<>""),$B2:B2),"")) and press Enter. This uses column C as the current month and column B as the previous range.
Drag the fill handle from the lower-right corner of the result cell across all month columns and down to additional data rows.
Select the result cells, go to the Home tab, open the Number Format drop-down, and choose Percentage.
Calculate Month-over-Month Percentage Changes in WPS Spreadsheets
Use WPS Spreadsheets to calculate percentage changes that ignore blank months with a single IF, LOOKUP, and IFERROR formula. It works directly with your existing monthly workbook layout and can be filled across and down in seconds.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel-compatible workbook, and click the worksheet that contains the monthly values.
- 2. Enter the formula in the first result cell: Click the first percentage-change cell and type the IF, LOOKUP, and IFERROR formula described above, then press Enter.
- 3. Fill and format the results: Drag the fill handle across and down, then select the results and choose Percentage from the Home tab number format.
Frequently Asked Questions
Why does my percentage change formula return a #N/A error for the first month?
The first month has no earlier value to compare with. The IFERROR function returns an empty string instead of #N/A. Start the formula in the second month or leave a previous data column for the first result.
How does the LOOKUP formula find the most recent nonblank value?
LOOKUP(2,1/($B2:B2<>""),$B2:B2) creates an array of 1 for nonblank cells and an error for blanks. LOOKUP ignores errors and returns the last valid numeric value in the preceding range.
Can I apply this formula when my data is in rows instead of columns?
Yes. Transpose the range logic: fix the starting row and expand the current row reference. The same IF, LOOKUP, and IFERROR pattern works with a vertical previous range.
How do I format the formula results as percentages?
Select the formula cells, go to the Home tab, open the Number Format drop-down, and choose Percentage. You can also press Ctrl+1, choose Percentage, and set decimal places.
Will this formula work in Microsoft Excel and WPS Spreadsheets?
Yes, the formula uses standard functions supported by both WPS Spreadsheets and Microsoft Excel. If you save the file as .xlsx, the calculation remains compatible.
