Competitive Pricing Formulas: CPI, Price Position, and Gap Analysis (With Excel)

There's no single "competitive pricing formula." There are four, and each one answers a different question about where you stand relative to the market.

I've spent years watching mid-market distributors try to price competitively using gut feel and whatever their reps heard on the last sales call. That doesn't scale when you've got 10,000+ SKUs. What scales is math. Specifically, four formulas that give you a real, repeatable read on your competitive position.

This is the reference page. Bookmark it. Every formula below includes the math, a worked distribution example, and an Excel formula you can copy straight into your spreadsheet.

For the broader strategy behind when and how to use these numbers, see our competitive pricing guide.

Formula 1: Competitive Price Index (CPI)

The Competitive Price Index is the single most useful number in competitive pricing. It tells you, on a scale around 100, where your price sits relative to the market average.

A CPI of 100 means you're priced exactly at the market average. Above 100, you're more expensive. Below 100, you're cheaper.

Worked example: Industrial fastener distributor

You sell a 100-pack of hex bolts for $18.75. You track four competitors:

Your CPI is 103.4. You're priced 3.4% above the competitive average on this SKU. Whether that's a problem depends on context. If you offer same-day delivery and the others ship in 3-5 days, a 3.4% premium is easily justified. If you're all selling the exact same product with identical service, you'll lose the price shoppers.

Excel formula:

Assuming your price is in B2 and competitor prices are in C2 through F2:

=ROUND((B2/AVERAGE(C2:F2))*100, 1)

Interpreting CPI at scale:

The real power of CPI shows up when you calculate it across your entire catalog. Sort by CPI and you'll see three groups:

A distributor I worked with ran CPI on their top 500 SKUs and found 120 of them sitting below 92. They'd been underpricing those items for years because their cost-plus markup hadn't kept pace with competitor price increases. Adjusting just those 120 SKUs added $340,000 in annual gross profit without losing a single customer.

Formula 2: Price Position Percentage

CPI compares you to the average. Price Position Percentage tells you where you fall within the range from cheapest to most expensive competitor.

A result of 0% means you're the cheapest. 100% means you're the most expensive. 50% puts you in the middle of the pack.

Worked example: Electrical supply distributor

You sell a 200-amp panel for $312. Competitor prices range from $285 to $345.

You're positioned at 45%: slightly below the midpoint of the competitive range. That's a solid position for a distributor that competes on reliability and availability rather than rock-bottom price.

Excel formula:

Assuming your price is in B2 and competitor prices are in C2 through G2:

=ROUND(((B2-MIN(C2:G2))/(MAX(C2:G2)-MIN(C2:G2)))*100, 1)

Why this matters more than CPI sometimes:

CPI can mislead you when there's an outlier. Say four competitors price a product at $50, $51, $52, and $90. The average is $60.75, and your $53 price gives you a CPI of 87.2, making it look like you're way below market. But Price Position % tells a more honest story:

You're at the very bottom of the range. That CPI of 87 wasn't because you're cheap. It was because one expensive outlier skewed the average. Price Position % catches that.

Formula 3: Price Gap Analysis

Price Gap tells you the percentage difference between your price and a specific competitor's price. It's the most granular of the four formulas: one-to-one, not one-to-many.

Positive means you're more expensive. Negative means you're cheaper.

Worked example: Plumbing supply distributor

You want to understand your position against your top 3 competitors on a commercial faucet valve:

You're 8.9% above Competitor A (the low-price leader), 2.2% below Competitor B, and 4.7% above Competitor C. If A is winning deals away from you, that 8.9% gap is the number your sales team needs to understand.

Excel formula:

Assuming your price is in B2 and a competitor's price is in C2:

=ROUND(((B2-C2)/C2)*100, 1)

Using gaps to set pricing rules:

Once you've calculated gaps across your catalog, you can build rules into your pricing matrix. For example:

• On commodity SKUs: maintain a gap of -2% to +3% against the market leader
• On specialty SKUs: allow gaps up to +15% if you're the stocking distributor
• On private label: price at -5% to -10% vs. the branded equivalent

These gap-based rules are more market-aware than flat cost-plus markups. A 30% markup might put you 12% above market on one product and 8% below on another. Gap-based rules keep you positioned consistently.

For more on how to build a complete competitive pricing analysis, see our step-by-step breakdown.

Formula 4: Competitive Price Band

The Price Band defines the range within which competitors price a given product. It's not about where you are. It's about where everyone is.

Worked example: HVAC parts distributor

You're analyzing a condensate pump across 6 competitors:

The competitive band is $22 wide, spanning a 15.8% range. The midpoint is $150.

Excel formulas:

Assuming competitor prices are in C2 through H2:

Band Width:   =MAX(C2:H2)-MIN(C2:H2)
Band Width %: =ROUND(((MAX(C2:H2)-MIN(C2:H2))/MIN(C2:H2))*100, 1)
Midpoint:     =(MAX(C2:H2)+MIN(C2:H2))/2

What the band width tells you:

Narrow bands (under 10%) signal commodity products where the market has converged on a price. There's little room to differentiate on price alone. Wide bands (over 20%) usually mean there's real differentiation happening: some suppliers bundle services, carry premium brands, or serve different customer tiers.

Here's a framework I've seen work well:

Putting the four formulas together

Each formula gives you one angle. Used together, they give you a complete competitive pricing picture. Here's how they connect on a single SKU:

Product: 3/4" brass ball valve

Now you can make an informed call. A CPI of 105.6 on a commodity item with a 22% band is reasonable if your fill rate is 95%+ and you ship same-day. It's not if you're selling the exact same product with identical service levels. The formulas don't make the decision. They give you the numbers to make a good one.

Building a competitive pricing tracker in Excel

Here's a practical layout for tracking competitive pricing across your key SKUs. Set it up once, update the competitor prices periodically, and the formulas do the rest.

Column layout:

Copy row 2's formulas down for every SKU. Add conditional formatting: red when CPI exceeds 110, green when it drops below 90. That gives you a visual dashboard of your competitive position across the entire catalog without any fancy software.

Pro tip: Don't try to track every SKU. Start with your top 100 by revenue. Those likely represent 40-60% of your total sales. Get the competitive picture right on those first, then expand.

Common mistakes with competitive pricing formulas

I've seen these trip up pricing teams at companies of every size.

Mistake 1: Comparing different units of measure. Your price is per box of 100. The competitor quotes per piece. Your CPI looks like 8,500 and you panic. Always normalize to the same UOM before calculating. In distribution, this is a constant headache with different pack sizes, case quantities, and per-foot vs. per-roll pricing.

Mistake 2: Using list price when the market trades on net. If your competitors' published list prices are $50 but every customer gets a 15% discount, the real market price is $42.50. Your CPI against list prices is meaningless. Use street price (what customers actually pay) whenever possible.

Mistake 3: Ignoring freight and delivery terms. A competitor's price is $100 delivered; yours is $95 plus $8 freight. You think you're cheaper. The customer sees $103 vs. $100. For a deep look at how these hidden costs erode margin, see our guide on the cost-plus pricing formula.

Mistake 4: Calculating CPI on stale data. A competitive price from 6 months ago isn't a competitive price. Markets move. If you're running competitive analysis quarterly, you're making decisions on where the market was, not where it is. The highest-impact SKUs need monthly or even weekly updates.

Mistake 5: Averaging across too many SKUs. An average CPI of 101 across 5,000 SKUs sounds great. But if 500 are below 85 (leaving money on the table) and 500 are above 115 (losing deals), the average hides the real problem. Always look at the distribution, not just the mean.

When competitive pricing formulas aren't enough

These formulas tell you where you stand. They don't tell you where you should be. That's a strategy question.

A CPI of 112 on a specialty part where you're the only local stocking distributor might be exactly right. A CPI of 98 on a commodity where you have higher costs than the competition might be unsustainable.

Competitive pricing formulas work best alongside other inputs:

• Your cost floor. The gross margin formula tells you the minimum price you can accept. Pricing below cost-plus just to match a competitor is a losing game.
• Customer willingness to pay. Some segments will pay a premium for availability, technical support, or consolidated billing. Competitive data alone can't tell you that.
• Strategic intent. Are you defending share, gaining share, or maximizing margin? Different objectives demand different positions within the competitive band.

For a full breakdown of how competitive pricing fits with other competitive pricing models, see our detailed comparison.

Next steps

If you're tracking competitor prices in a spreadsheet today, plug these four formulas in. Start with CPI on your top 100 SKUs. That alone will show you where you're leaving money on the table and where you're overpriced.

The hard part isn't the math. It's getting current, accurate competitive data and maintaining it across thousands of SKUs. If your pricing team is spending more time collecting data than acting on it, that's a sign your process needs to change.

Pryse helps mid-market distributors and manufacturers spot exactly where their prices are out of line, without the 6-month implementation of enterprise pricing tools. Upload your data and see your competitive position in 24 hours.