Formula Errors

How to Round Prices to 0.05 or 0.09 in Excel

Khadija Khan Sep 30, 2026 868 views

Question details

The user needs to round calculated prices to specific decimal endings, such as 0.05 or 0.09, using conditional formulas based on custom ranges.

How to Use an Excel Formula for Rounding Prices to 0.05 or 0.09

Product: Excel
Device & OS: not provided
Scenario: Applying specific retail pricing strategies to a list of calculated prices where standard rounding rules do not apply.
Observed behavior: Prices need to be dynamically adjusted based on their extracted decimal values to meet defined psychological pricing rules.

Before you start

Before applying these formulas, ensure your original price data is formatted as numbers and define exactly which decimal ranges should trigger each specific rounding condition.

Solution 1Recommended

Use a Nested IF and MOD Formula for Custom Rounding

This method allows you to define exact decimal ranges and apply specific rounding rules using the MOD, ROUNDUP, and ROUNDDOWN functions.

The MOD function extracts the decimal part of a number, allowing you to evaluate it with the IF function. Based on the extracted decimal, you can use ROUNDUP or ROUNDDOWN combined with addition to reach the desired .05 or .09 ending.

Select the target cell

Click the empty cell where you want the new rounded price to appear.

Enter the conditional formula

Type the formula: =IF(OR(MOD(H12,1)>=0.95,MOD(H12,1)=0),ROUNDUP(H12,0),IF(AND(MOD(H12,1)>=0.51,MOD(H12,1)<0.95),ROUNDDOWN(H12,1)+0.09,H12))

Adjust the cell reference

Replace all instances of 'H12' in the formula with the cell reference that contains your original price.

Apply across your dataset

Press Enter to calculate the result, then click and drag the fill handle at the bottom right of the cell downwards to apply this pricing rule to the rest of your list.

Use a Nested IF and MOD Formula for Custom Rounding

Customize Ranges: You can adjust the logical operators (like >=0.51) and the added values (like +0.09) in the formula to match your exact pricing strategy and thresholds.

Solution 2

Use the MROUND or CEILING Functions for Standard 0.05 Rounding

If you strictly want to round to the nearest 0.05 without complex conditional rules, Excel's dedicated rounding functions are much faster.

Manage Your Pricing Strategies with Ease

Use WPS Spreadsheet for Advanced Formula Calculations

WPS Spreadsheet fully supports all standard Excel formulas, including complex nested IF statements, MOD, and rounding functions. It is an excellent tool for managing retail pricing strategies and bulk price adjustments.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your pricing data.
2. Apply the pricing formula: Select the target cell, type in your nested IF and MOD formula, and update the cell references.
3. Drag to fill: Press Enter to see the calculated custom rounded price, then drag the fill handle to apply it across your entire product list.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Free and lightweight alternative to Microsoft Office.Built-in function helper for easy formula creation and syntax checking.Smooth handling of large datasets for bulk pricing updates.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the MOD function used in pricing formulas?

The MOD function with a divisor of 1 (e.g., MOD(A1, 1)) isolates the decimal portion of a number. This allows the formula to evaluate only the cents portion of a price, ignoring the base dollar amount, which is essential for creating custom rounding rules based on decimal endings.

How can I automatically round all prices up to end in .99?

To always end a price in .99 regardless of the current decimal, you can use the formula =INT(A1)+0.99 if you want to keep the same base dollar amount, or =ROUNDUP(A1,0)-0.01 to round up to the next highest whole dollar and subtract one cent.

Does WPS Office support these complex nested formulas?

Yes, WPS Spreadsheet supports all standard and complex functions found in Microsoft Excel, including IF, AND, OR, MOD, ROUNDUP, and ROUNDDOWN. Your pricing formulas will work seamlessly without requiring any syntax modifications.

Excel & Data Problems