How to Round Excel Prices to the Nearest .04 or .09 in WPS Office Spreadsheets
Question details
The user needs to round price data in a spreadsheet so every result ends in .04 or .09, for example changing 5.88 to 5.89 and 5.56 to 5.54.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a price list and applying a custom cent-ending rounding rule instead of standard rounding.
- Observed behavior
- Standard rounding functions do not produce values ending specifically in .04 or .09.
Confirm that the source prices are stored as numbers rather than text, and identify the first price cell reference, such as A1, so you can adjust the formula correctly.
Use the MROUND Formula to Round Prices to the Nearest .04 or .09
Use this one-formula solution when you want the closest value ending in .04 or .09 for a price list.
The formula shifts the source value by 0.01, rounds to the nearest 0.05 interval, and then subtracts 0.01. This makes the final cent amount land on either .04 or .09. For example, 5.88 becomes 5.89 and 5.56 becomes 5.54.
Click the empty cell where the rounded price should appear, for example B1 next to the first price in A1.
Type =MROUND(A1+0.01,0.05)-0.01 and press Enter. Replace A1 with the actual cell that contains the first price.
Drag the fill handle or copy the formula down the column to round the remaining prices.
Round Price Lists to .04 or .09 in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use the Excel-compatible MROUND formula to round prices to the nearest .04 or .09 ending. WPS Office supports .xlsx files, formulas, and a familiar spreadsheet interface so the transition is seamless.
- 1. Open the price workbook: Launch WPS Office, click the file location or use the navigation pane to open the .xlsx workbook that contains the prices.
- 2. Select the result cell: Click the first empty cell beside a price, such as B1, where you want the rounded value to appear.
- 3. Enter the rounding formula: Type =MROUND(A1+0.01,0.05)-0.01 and press Enter. Replace A1 with the cell that contains the first price.
- 4. Fill the formula down: Drag the fill handle from the formula cell down through the price rows to apply the formula to the entire list.
- 5. Review midpoint prices: Check any values exactly halfway between valid endings and adjust the formula if your rounding rule requires the lower or higher result.
Frequently Asked Questions
Why does the formula add 0.01 before using MROUND?
Adding 0.01 shifts the price so that rounding to the nearest 0.05 interval and then subtracting 0.01 makes the final value end in .04 or .09 instead of a normal 0.05 interval.
Can I use MROUND in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports Excel-compatible functions including MROUND, so you can use the formula in a WPS workbook the same way you would in Excel.
What should I do if my price is in a different cell than A1?
Replace A1 in the formula with your actual cell reference. For example, if the first price is in C2, use =MROUND(C2+0.01,0.05)-0.01 and copy it down.
How do I handle a price that is exactly halfway between two .04 or .09 endings?
The basic MROUND formula follows standard midpoint rounding behavior. If your business rules require a specific lower or higher result, test sample values and adjust the formula, for example by using FLOOR instead of MROUND for a conservative lower result.
