'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Round Excel Prices Down to End in .98
Question details
The user needs to round retail prices down to the nearest whole-dollar amount minus two cents, ensuring prices always end in .98.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting retail or promotional prices to end in .98 for marketing and sales purposes.
- Observed behavior
- The current prices consist of varying decimal values, and the goal state is a standardized price list where every value consistently ends in .98.
Ensure your price data is formatted as numbers or currency, and identify the specific column containing the original prices before applying the formula.
Use the ROUNDDOWN Function to Standardize Prices
Apply a combination of the ROUNDDOWN function and simple subtraction to force any numerical value to end in .98.
The ROUNDDOWN function in Excel rounds a number toward zero. By adding an offset of 0.02 before rounding, and then subtracting 0.02 after rounding to zero decimal places, you can successfully anchor any price to end in .98 without dropping an extra dollar by mistake.
Click on an empty cell in the same row where you want the new adjusted retail price to appear.
Type =ROUNDDOWN(B2+0.02,0)-0.02 into the formula bar (assuming your original price is located in cell B2).
Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom right corner of the cell) downwards to apply this pricing formula to the rest of your data list.
Easily Manage Pricing Strategies with WPS Spreadsheet
WPS Spreadsheet fully supports all standard Excel formulas, including ROUNDDOWN. It is a powerful, free tool for managing retail pricing strategies, creating marketing reports, and analyzing large datasets efficiently.
- 1. Open Your Pricing Sheet in WPS: Launch WPS Office and open the spreadsheet containing your retail price list.
- 2. Input the Pricing Formula: In an empty column next to your prices, type =ROUNDDOWN(A2+0.02,0)-0.02 to apply the .98 adjustment.
- 3. Batch Apply Automatically: Double-click the fill handle on the selected cell to instantly update hundreds of retail prices in one go.
Frequently Asked Questions
Can I round prices to end in .99 instead of .98?
Yes. To make prices end in .99, adjust the offset values in the formula. Use =ROUNDDOWN(B2+0.01,0)-0.01. This accurately targets .99 for psychological pricing.
Why do we add 0.02 before rounding down?
Adding 0.02 ensures that values already ending in .98 or .99 are pushed to the next whole dollar before the formula rounds down to zero decimal places. This prevents a price like $10.98 from incorrectly dropping down to $9.98.
How can I convert the formula results into static numbers?
Select the column with your new calculated prices, press Ctrl+C to copy them, right-click on the same selection, and choose 'Paste Special' > 'Values'. This removes the formulas and leaves only the fixed price numbers.
