How to Calculate the Percentage Difference Between New and Old Scores in WPS Office Excel
Question details
The user needs to calculate the percentage change between a new score and an old score after matching words between two Excel lists.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has two lists with matched words, old scores in one column, and new scores in another, and wants to calculate the percentage change from old to new for each matched row.
- Observed behavior
- The result column should display the percentage difference as a percentage, with blank cells where the calculation is not possible.
Make sure the old score is in column B, the new score is in column D, and each row contains the matched word for the two values before you enter the formula.
Use IFERROR with D2/B2-1 to Calculate the Percentage Change
This is the direct formula solution when you need the percentage increase or decrease between a new score in column D and an old score in column B, with errors hidden automatically.
The formula divides the new score by the old score and subtracts 1. The IFERROR wrapper returns an empty cell instead of #DIV/0! or other errors when the old score is missing or zero.
Click cell E2 in the row where the old score is in column B and the new score is in column D.
Type =IFERROR(D2/B2-1,"") in cell E2 and press Enter.
Drag the fill handle from cell E2 down through the remaining rows to apply the formula to all matched scores.
Select the result cells, go to the Home tab, and choose Percentage from the Number format drop-down list.
Calculate New vs. Old Score Percentage Difference in WPS Office Spreadsheets
Use WPS Office Spreadsheets to quickly compute the percentage change between old and new scores with the same IFERROR formula, format results as percentages, and keep your workbook fully compatible with Microsoft Excel.
- 1. Open your workbook: Open the Excel workbook in WPS Office Spreadsheets.
- 2. Enter the formula: Click cell E2 and enter =IFERROR(D2/B2-1,"").
- 3. Apply the formula to all rows: Drag the fill handle from cell E2 down through the remaining data rows.
- 4. Format as percentage: Select the result cells, go to the Home tab, and choose Percentage from the Number format drop-down list.
- 5. Save the workbook: Save the file as an .xlsx workbook to keep it compatible with Microsoft Excel.
Frequently Asked Questions
Why does the formula return a blank cell instead of an error?
The IFERROR function catches errors such as #DIV/0! and returns an empty string, so rows with a missing or zero old score display blank instead of an error.
Can I calculate the percentage difference if the old score is in column A and the new score is in column C?
Yes. Adjust the formula to =IFERROR(C2/A2-1,"") and fill the formula down through the result column.
How do I display the result as a percentage with two decimal places?
Select the result cells, press Ctrl+1 to open Format Cells, choose Percentage, and set the number of decimal places to 2.
Does this formula show both percentage increase and decrease?
Yes. A positive result indicates a percentage increase, a negative result indicates a percentage decrease, and zero means there is no change.
