Function Problems

Apply a Distributor Cost Price Threshold in Excel

Camila MilosovichCamila Milosovich Nov 14, 2026 868 views

Question details

Create a pricing formula that enforces a minimum selling price based on distributor cost and tax, and round calculations consistently.

Apply a Distributor Cost Price Threshold in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A pricing worksheet needs to compare calculated selling prices with a distributor-based threshold.
Observed behavior
The selling price should not fall below the applicable threshold, and worksheet results should align with consistently rounded calculator results.
Before you start

Identify the cells containing the proposed selling price, distributor cost, and tax rate. Confirm whether the rate is entered as a percentage and whether tax should be added to or excluded from the threshold.

Solution 1Recommended

Enforce a distributor cost floor with MAX and ROUND

Use this formula when the selling price must be at least the distributor cost plus tax.

In this example, I12 contains the proposed selling price, C12 contains the distributor cost, and J2 contains the tax rate. Replace these references if your worksheet uses different cells.

1
Check the inputs

Enter the proposed selling price in I12, the distributor cost in C12, and the tax rate as a percentage in J2.

2
Enter the price floor formula

In an empty result cell, enter =MAX(I12,ROUND(C12*(1+J$2),2)). MAX returns the higher of the proposed price and the rounded cost-plus-tax threshold.

3
Apply it to other rows

Drag the result cell's fill handle down the column. The J$2 reference keeps the tax rate on row 2 while the row-specific inputs change.

4
Check the rounding

Compare the result with a calculator using the same rounding points. If intermediate worksheet values must be rounded to cents, wrap those calculations in ROUND(formula,2) as well.

Enforce a distributor cost floor with MAX and ROUND
If I12 is already the minimum: For a worksheet where I12 is the minimum and J2 is a rate subtracted from C12, use =MAX(I12,ROUND(C12-(J$2*C12),2)). This formula does not calculate cost plus tax.
Work with pricing formulas in WPS

Apply the same price threshold in WPS Spreadsheet

Use MAX and ROUND in WPS Spreadsheet to keep a proposed selling price at or above a distributor cost-plus-tax threshold.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel pricing workbook.
  2. 2. Check the inputs: Locate the proposed price, distributor cost, and tax rate cells. For the example, use I12, C12, and J2 respectively.
  3. 3. Add the formula: Select an empty result cell and enter =MAX(I12,ROUND(C12*(1+J$2),2)).
  4. 4. Fill and save: Drag the fill handle down for other pricing rows, then use File > Save As to save the workbook in XLSX format.
Enforce a cost-plus-tax price floor with a formulaOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet functions and cell referencesWork with spreadsheets in a free, easy-to-use office suite
QA img-9

Frequently Asked Questions

What happens if the proposed selling price is already above the threshold?

MAX returns the proposed price. For example, =MAX(I12,ROUND(C12*(1+J$2),2)) returns I12 when it is higher than the rounded cost-plus-tax amount.

Why does Excel show a different result from my calculator?

Excel may calculate with more decimal places than are displayed. Use ROUND(formula,2) at the same calculation stages you round on the calculator, rather than relying only on cell formatting.

Can I use IF instead of MAX?

Yes. For the example cells, use =IF(I12<ROUND(C12*(1+J$2),2),ROUND(C12*(1+J$2),2),I12) to return the threshold when the proposed price is lower.

Does the threshold flag treat a price equal to the minimum as too low?

No. The formula =--(MIN(D2,F2,G2)<B2) returns 1 only when the lowest price is strictly below B2. Replace < with <= if equality should also be flagged.