Apply a Distributor Cost Price Threshold in Excel
Question details
Create a pricing formula that enforces a minimum selling price based on distributor cost and tax, and round calculations consistently.

- 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.
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.
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.
Enter the proposed selling price in I12, the distributor cost in C12, and the tax rate as a percentage in J2.
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.
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.
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.

Flag prices that fall below a threshold
Use a flag when you want to identify a below-threshold price without changing it.
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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel pricing workbook.
- 2. Check the inputs: Locate the proposed price, distributor cost, and tax rate cells. For the example, use I12, C12, and J2 respectively.
- 3. Add the formula: Select an empty result cell and enter =MAX(I12,ROUND(C12*(1+J$2),2)).
- 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.

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.




