Why Excel SUM Shows Unexpected Rounded Totals in WPS Office
Question details
The user needs to understand why SUM returns rounded-looking totals and how to ensure intermediate VAT and remaining amount calculations are rounded to two decimal places.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is calculating VAT-inclusive amounts, applying a two-decimal number format, and expects SUM to total the displayed rounded values.
- Observed behavior
- SUM adds the underlying stored values, so the total may appear off by a few cents compared with the displayed rounded values.
Open the workbook in WPS Office Spreadsheets and locate the VAT or expense columns that show two-decimal values. Check whether the cells currently contain plain SUM or division formulas or already include the ROUND function.
Use ROUND in Each Intermediate VAT Formula
Use this when every intermediate VAT or unit price calculation must be rounded to two decimal places before summing.
Number formatting only changes how a cell looks; it does not change the actual stored value. Adding ROUND to the formula makes the stored value match the two-decimal display.
Click the cell where the intermediate VAT result should appear, for example E2.
Type =ROUND(C2/1.2,2) and press Enter to round the VAT-exclusive amount to two decimal places.
Drag the fill handle from E2 down through the remaining data rows.
In the next column, enter =C2-E2 to subtract the rounded VAT result from the inclusive amount.
Round Only the Final SUM Total
Use this if the intermediate rows can keep their full precision and only the final total must display as two decimals.
Fix Excel SUM Rounded Totals with ROUND in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and apply ROUND to intermediate VAT or final SUM formulas so totals reflect the stored two-decimal values instead of only the display format.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx file that contains the VAT or SUM calculations.
- 2. Edit the first intermediate formula: Click the result cell and enter =ROUND(C2/1.2,2) or the appropriate cell reference, then press Enter.
- 3. Copy the formula down: Drag the fill handle down the column to apply ROUND to all intermediate rows.
- 4. Adjust the remaining amount formula: Enter =C2-E2 in the adjacent column and copy it down.
- 5. Check the final total: Select the SUM cell and confirm the total matches the rounded two-decimal values.
Frequently Asked Questions
Why does my SUM formula add values that look different from the cell display?
SUM adds the underlying stored values, not the values shown by the number format. If a cell displays 10.00 but stores 10.004, SUM uses 10.004.
How do I round VAT to two decimal places in WPS Office Spreadsheets?
Use the ROUND function in the VAT result cell, for example =ROUND(C2/1.2,2), and then copy the formula down the column.
What formula should I use to subtract rounded VAT from the total?
Use =C2-E2 where C2 contains the VAT-inclusive amount and E2 contains the rounded VAT result. Copy the formula down as needed.
Does the ROUND function affect only the display or the stored value?
ROUND changes the stored result of the formula to the specified number of digits, so later SUM formulas use the rounded value rather than the unrounded underlying value.
Can I fix this by changing the number format?
No. Number formatting only changes how values appear. To make the stored values match the displayed two-decimal amounts, use ROUND in the relevant formulas.
