How to Round a Decimal to the Nearest One-Third in WPS Spreadsheets
Question details
The user needs a reliable spreadsheet formula to round a decimal to the nearest one-third increment, with correctly formatted results.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has decimal numbers in a worksheet and wants to round them to the nearest increment of one-third, producing values such as 1, 1.333, 1.667, and 2.
- Observed behavior
- The desired result should show the nearest one-third increment with enough decimal places to distinguish values such as 1.333 and 1.667.
Confirm the cells containing the source decimal values use Number format and check whether your version of WPS Spreadsheets supports MROUND. If MROUND returns #NAME?, use the ROUND alternative formula.
Use the MROUND Formula with 1/3 as the Significance
This is the most direct method and works in WPS Spreadsheets versions that support MROUND.
Click the empty cell where you want the rounded value to appear, for example B1 if your decimal is in A1.
Type =MROUND(A1,1/3) and reference the cell containing the decimal value.
Press Enter, then go to Home > Number Format and set enough decimal places so results display values such as 1.333 or 1.667.
Use =ROUND(A1*3,0)/3 When MROUND Is Unavailable
This alternate formula produces the same one-third rounding and works without MROUND.
Round Decimals to One-Third Increments Directly in WPS Spreadsheets
Use WPS Spreadsheets and the built-in MROUND or ROUND formulas to round decimal values to the nearest one-third, then format the cells to display results like 1.333, 1.667, or 2 clearly.
- 1. Open WPS Spreadsheets: Launch WPS Office, open the workbook containing the decimal values, or create a new spreadsheet.
- 2. Select the result cell: Click the empty cell where you want the rounded value to appear.
- 3. Enter the rounding formula: Type =MROUND(A1,1/3) or =ROUND(A1*3,0)/3, replacing A1 with the cell that contains your decimal.
- 4. Apply number formatting: Press Enter, then go to Home > Number Format and increase the decimal places to display results such as 1.333, 1.667, or 2.
Frequently Asked Questions
Why does =MROUND(A1,1/3) return a #NAME? error?
The #NAME? error usually means your WPS Spreadsheets version does not recognize MROUND. Use the alternate =ROUND(A1*3,0)/3 formula instead, or update to a version that supports MROUND.
How do I show 1.333 and 1.667 instead of rounded whole numbers?
Select the result cells, go to Home > Number Format, choose Number, and increase the decimal places to at least 3 so values such as 1.333 and 1.667 are visible.
Can I round an entire column of decimals to the nearest one-third at once?
Yes. Enter the formula in the first result cell, then drag the fill handle down the column. Make sure each formula references the corresponding source cell for that row.
Will these formulas work when I open the file in Microsoft Excel?
Yes. MROUND(A1,1/3) and ROUND(A1*3,0)/3 are standard Excel-compatible formulas and will calculate the same way in WPS Spreadsheets and Microsoft Excel.
