Sales Pipeline Analysis in a Spreadsheet

Your CRM shows a list of deals, not whether the forecast is defensible. Here are the six formulas that expose where deals stall and what the pipeline is really worth — stage conversion, win rate, aging, velocity, coverage, and a weighted forecast.

By the Quiriz Team · Published August 5, 2026 · 7 min read

A CRM gives you a list of deals. Six formulas turn that list into an answer to "where do deals stall, and is the forecast real?" All of them are SUMIFS / COUNTIFS / SUMPRODUCT — no macros.

1. Stage conversion

Count deals that reached each stage, then divide each by the prior stage. If 120 reached Demo and 40 reached Proposal, Demo→Proposal converts at 33%. The biggest drop is where to focus.

=COUNTIF(Stage, "Proposal") / COUNTIF(Stage, "Demo")

2. Win rate

Of closed deals, what share are won: =COUNTIF(Stage,"Closed Won") / COUNTIFS(Stage,"Closed*"). Decide deliberately whether open deals belong in the denominator.

3. Stage aging

Days a deal has sat in its current stage: =TODAY()-[Stage Entered]. Sort descending to find the stalled deals the forecast is quietly leaning on.

4. Sales velocity

Revenue the pipeline produces per day: (open opps × win rate × avg deal) ÷ cycle days. Raising win rate or shrinking the cycle both move it.

5. Coverage ratio

Open pipeline ÷ quota: =SUMIF(Stage,"<>Closed*",Amount) / Quota. If you close 1 in 4, 3x coverage is thin — coverage should scale inversely with win rate.

6. Weighted forecast

Each open deal's amount × its stage probability, summed: =SUMPRODUCT(Amount, Probability). A defensible commit instead of a gut number.

Keeping "win rate" meaning one thing

These formulas only agree if everyone agrees what "won", "qualified", and each stage probability mean. Point Quiriz at the deals export and an admin defines Win Rate, Weighted Forecast, and the stage glossary once in Company Context — so the rep, the report, and the Slack question all return the same figure.

Quiriz Company Context editor with Won Revenue, Win Rate, and Weighted Forecast metric definitions — Win rate, weighted forecast, and stage terms defined once — the same math on every query.

Ask your pipeline the hard questions

Upload your deals export and ask Quiriz for conversion, velocity, and a weighted forecast in plain English — consistent definitions, every time. Free to start.

Try Quiriz free →

Frequently asked questions

How do I calculate stage conversion rate in a spreadsheet?

Count how many deals reached each stage (COUNTIF on the stage column, or a count of deals that passed through it), then divide each stage count by the previous stage count. Stage 3 count / Stage 2 count is the Stage-2-to-3 conversion rate.

What is the sales velocity formula?

Sales velocity = (number of open opportunities x win rate x average deal size) / average sales cycle length in days. It tells you how much revenue the pipeline generates per day. Improve any one input and velocity rises.

What is pipeline coverage ratio?

Coverage = total open pipeline value / quota (or target) for the period. A common rule of thumb is 3x-4x, but the right number depends on your win rate — lower win rate needs more coverage. Compute it as =SUMIF(stage_col, "<>Closed*", amount_col) / quota.

How do I build a weighted forecast?

Assign each stage a win probability, then weighted forecast = sum of (deal amount x stage probability) over open deals. In a spreadsheet: =SUMPRODUCT(amount_range, probability_range). It is more defensible than a gut-feel commit number.