Formula Errors

How to Calculate Net Prices with Tiered Discounts in Excel

Nimra Malik Oct 10, 2026 871 views

Question details

The user needs an Excel formula to calculate a net price by applying a structured discount model: checking for a product hierarchy discount first, a division discount second, and falling back to a default 40% discount if neither is found.

How to Calculate Net Prices with Tiered Discounts in Excel

Product: Spreadsheet
Device & OS: not provided
Scenario: Applying multiple conditional discounts to a pricing list based on a lookup table to automatically calculate accurate net prices.
Observed behavior: Requires nesting lookup functions to establish priority conditions and precise currency formatting to display the calculated net price correctly.

Before you start

Ensure your discount table is correctly organized and defined as a Named Range (e.g., 'ZEUDDiscountTbl') to make your lookup formulas cleaner and prevent referencing errors.

Solution 1Recommended

Use Nested IFERROR and VLOOKUP Functions

Combine the IFERROR, VLOOKUP, and LEFT functions to evaluate tiered discount conditions sequentially until a matching discount rate is found.

This approach guarantees that Excel checks for the most specific discount first (product hierarchy). If that lookup results in an error, the first IFERROR function triggers the next VLOOKUP (division discount). If that also fails, the final IFERROR provides the default 40% discount.

The N() function ensures that any text or blank cells evaluated during calculation are treated as numeric zero to prevent #VALUE! errors.

Create a Named Range for the discount table

Select the range of cells containing your discount rates. Click into the Name Box (to the left of the formula bar), type 'ZEUDDiscountTbl', and press Enter.

Enter the nested lookup formula

In your target net price cell (e.g., Z4), input the formula: =IF($M4="","",N($M4)-N($M4)*(IFERROR(VLOOKUP(LEFT($I4,5),ZEUDDiscountTbl,4,FALSE),IFERROR(VLOOKUP($H4,ZEUDDiscountTbl,4,FALSE),40%))))

Copy the formula down the column

Hover over the bottom-right corner of cell Z4 until the cursor turns into a crosshair, then click and drag down to apply the calculation to the remaining rows in your table.

Apply precise currency formatting

Select the calculated net price cells, right-click, and choose 'Format Cells'. Navigate to the 'Number' tab, select 'Currency', set the symbol to Euro (€), and adjust the decimal places to your required precision (e.g., 6 decimal places).

Use Nested IFERROR and VLOOKUP Functions

Extracting specific characters: The LEFT($I4,5) portion of the formula is crucial if your product hierarchy string is longer than the lookup value. It extracts only the first 5 characters (e.g., '1C050') to successfully match the discount table.

Advanced Spreadsheet Formulas

Calculate Complex Tiered Pricing Easily in WPS Spreadsheet

WPS Office Spreadsheet provides full support for advanced lookup formulas, named ranges, and exact number formatting to handle sophisticated tiered pricing models seamlessly.

1. Define your range: Open your pricing workbook in WPS Spreadsheet, navigate to the Formulas tab, and use the Name Manager to define your discount table as 'ZEUDDiscountTbl'.
2. Insert the net price formula: Paste the nested IFERROR and VLOOKUP formula into the net price column to automatically calculate your tiered discounts.
3. Format your results: Highlight your newly calculated cells, right-click to access Format Cells, and configure your desired currency and decimal precision.

Full compatibility with Microsoft Excel's VLOOKUP, IFERROR, and text-manipulation functionsIntuitive Name Manager for effortlessly defining lookup tables and dynamic rangesFlexible currency formatting options supporting specific decimal precisions like 6-digit EurosCompletely free, lightweight, and features an interface familiar to all spreadsheet users

microsoft office alternative - wps office

Frequently Asked Questions

How do I create a Named Range for my discount table?

Select the entire range of cells containing your discount data, click on the Name Box located to the left of the formula bar, type your preferred name without spaces (such as ZEUDDiscountTbl), and press Enter.

Why is the LEFT function used in the discount formula?

The LEFT function extracts a specific number of characters from the beginning of a text string. In this scenario, LEFT($I4,5) is used to isolate a 5-character product hierarchy code from a longer string so it matches the exact codes listed in the lookup table.

What does the N() function do in an Excel formula?

The N() function converts a value to a number. It is particularly useful in financial formulas because it forces empty text strings ("") or blank cells to be treated as a zero, preventing #VALUE! errors during multiplication or subtraction.

How do I format the net price result to display the Euro symbol with exact decimals?

Right-click the cells containing your results and select 'Format Cells'. In the dialog box, go to the Number tab, choose Currency, select the Euro (€) symbol from the drop-down list, and enter your required number of decimal places.

Excel & Data Problems