Log median and mode beside average deal size to show the middle deal and the most common amount.
Key takeaways
- Sort the deal list before finding its median.
- Check which amounts repeat most often before recording a mode.
- Record the deal count and basis beside the figures.
Why one average can mislead
It is quick to say, easy to multiply by the number of deals you expect to close, and it sounds like a solid fact about the pipeline. The trouble is that an average is one number that has to stand in for every deal, and it can be a poor stand-in.
The average, which spreadsheet tools call the arithmetic mean, adds up all the values and divides by how many there are. Every deal pulls on the result with the same weight. The effect of an unusually large or small deal depends on its distance from the other values.
That is the gap this post addresses. Logging two more measures beside the average gives the forecast a clearer picture. These are the median and mode. Together with the average, they let a revenue leader check the number they quote against the shape of the deals underneath it.
This is not a large change. The goal is not to throw out the average. The goal is to stop relying on it alone.
What the median tells you
The median is the middle value when the deals are sorted from smallest to largest. If the list has an odd number of deals, the median is the one in the center. If it has an even number, the median is the average of the two middle deals. Microsoft's documentation for its spreadsheet functions gives a simple example: the median of 2, 3, 3, 5, 7, and 10 is 4.
Work through that list yourself. There are six values, so the middle falls between the third and fourth. Those are 3 and 5, and their average is 4. The 10 at the top does not affect the median at all. If that 10 were replaced with a much larger value, the sorted list would still have 3 and 5 in the middle, and the median would still be 4. That is the point of the measure. The median does not move much when one deal is unusually large or small.
For a revenue leader, the median answers a plain question: if you picked the deal sitting in the middle of this pipeline, what would it be worth? When the median sits well below the average, inspect the deal sizes before attributing the gap to a few large deals. That does not make those large deals less important. It does mean the forecast should not assume that every deal will be that size.
The median also has a limit. It tells you about the middle position in the list, not about the total. A forecast built only on the median can understate the value of a few big deals that carry most of the revenue. This is why the median belongs beside the average, not in place of it. Each one answers a different question, and the gap between them is useful information.
What the mode tells you
The mode is the value that appears most often. In a list of deal sizes, it is the amount your team closes most frequently. Microsoft's documentation gives the matching example: the mode of 2, 3, 3, 5, 7, and 10 is 3. The value 3 appears twice, and every other value appears once, so 3 is the mode.
Real deal data does not always produce a clear answer. A list may have no value that repeats at all, in which case there is no meaningful mode, and the log should say so rather than guess. If values tie for the highest frequency, record all tied values, or note that the list has no single most common amount. Writing down the tie is more honest than picking one.
The mode is most useful when deals cluster around set prices. If the mode is a standard package price, that suggests your team is selling a product tier, and the forecast can be built from the tiers rather than from one blended number. If the mode is a discounted price, it may signal pricing pressure that the average would hide. Either way, the mode gives the forecast call a concrete amount that buyers are actually paying.
With only a handful of deals, one repeated value can appear to be the most common amount when it is really just chance. For that reason, log the number of deals alongside the mode, so the people reading the forecast can judge how much weight to give it.
Illustrative example: a large deal changes the average
Suppose a cleaned list contains deal amounts of 10, 10, and 70, all in the same currency and unit. The median is 10 because it occupies the middle position. The mode is also 10 because it appears most often. The average is higher because it includes the larger amount.
The large deal is real and may deserve a closer look on its own, but the forecast should not assume the next deal will look like that deal. Record the count and basis beside the figures so the comparison can be reproduced.
How to calculate the numbers in a spreadsheet
The average is the simplest to calculate, and Microsoft's documentation for its AVERAGEIFS function describes a version that averages only the cells meeting several conditions. The function returns the average (arithmetic mean) of all cells that meet multiple criteria. That makes it useful for splitting deals by region, product, owner, or quarter, so each segment gets its own average.
The same page describes the range to average as one or more cells to average, including numbers or names, arrays, or references that contain numbers. In practice, keep the deal amount in its own numeric column. Put notes, stage names, and temporary text in other columns, so that text is not mixed in with the figures you are averaging.
Segment averages need one more check. The same documentation shows that when no cells meet the criteria, the function returns a #DIV0! error. In a forecast, this means an empty segment, such as a region with no closed deals this quarter, will show an error. Treat the error as a signal that the segment has no data, not as a number to be replaced with zero. A zero would look like a real result and could mislead anyone reading the forecast. For related checks, see AVERAGEIFS errors.
Median and mode do not work the same way as AVERAGEIFS. Check whether your program provides median and mode functions and consult its help. Whatever you use, test it on a small list where you already know the answer, such as the six-value example above, before trusting it on the full pipeline.
Clean the deal history before you measure it
The median and mode are only as good as the data behind them. Before you calculate anything, check the list.
Nutshell's guide to moving CRM data makes a related point about migrations: teams that take the time to clean their data before migrating it typically see the best results. The same logic applies to forecast figures. Duplicate deals inflate the count of a common amount. Temporary values, such as a deal entered at a nominal amount while the price was still being negotiated, can distort both the median and the average. A single temporary amount that repeats across many records can even become the mode, which would make the log look sure of something that is not true.
Check for a few specific problems. Look for duplicate rows that describe the same customer and amount. Look for zero or near-zero amounts that are not real sales. Make sure every amount uses the same currency and the same unit, so that a figure entered in thousands is not mixed with figures entered in dollars. Confirm that the stage of each deal matches what you intend to measure. Each fix makes the three measures more trustworthy.
It also helps to keep a short note of what you removed and why. If a number changes on the next forecast, the note tells the reader whether the change came from the market or from a cleanup of the data.
Enforce the rules where the data is entered
Cleaning data once is useful. Preventing bad entries is better. Many database systems let you define rules that the data must follow. PostgreSQL's documentation on constraints describes the behavior directly: if a user attempts to store data in a column that would violate a constraint, an error is raised. Define the amount's numeric type and validation rules to reject entries you do not intend to measure.
Your CRM or spreadsheet may not offer database-style constraints, but the idea still applies. A drop-down for currency, a required field for the amount, or a validation rule that rejects negative values can prevent some of the errors described above. Set these rules before you start logging the three measures, so the figures you compare from one quarter to the next were built the same way. See CRM validation checks when reviewing data entry.
What to log beside the average
The log should be short enough to read during a call and complete enough that someone can reproduce it. A useful line includes four things: the average, the median, the mode, and the basis for the figures. The basis is the set of deals used, along with the number of deals in that set.
For example, a log line might read: average, median, mode, seven deals, closed-won this quarter, renewals excluded. Each part of that line answers a question someone will ask. What was measured? Over what period? Which deals were left out? How many deals sit behind the numbers?
Be consistent about the basis. If one quarter's average includes open pipeline and the next quarter's includes only closed-won deals, the figures cannot be compared. Decide on the rule once, write it down, and apply it every time. If you want to change the rule, record the change and recalculate the previous period on the new basis so the trend is fair.
Include the count every time. A median or mode from three deals carries much less weight than one from fifty. The count tells the reader how much to trust each figure, and it prevents a small sample from being quoted as if it were a broad pattern.
How to talk about a gap between the numbers
Most of the value of these measures comes from the gaps between them.
A good way to present this on a call is to quote the average, then state the median beside it, and then explain the difference in one sentence. For example: the average is above the median, and the gap comes from one large deal that closed early in the quarter. That sentence does two jobs. It gives the average its honest context, and it shows the team understands what is in the number.
When the mode is strong, mention it too. If the most common deal is a standard package, a forecast can be framed around how many packages the team expects to sell. That is often easier for a room to discuss than a single blended figure, and it links the forecast to the product choices that drive it.
Avoid two common mistakes. The first is to quote the median as if it were the average, which hides the large deals that may matter most for revenue. The second is to drop the average entirely in favor of the median because it looks more conservative. Both moves replace one incomplete picture with another. The aim is to show all three and explain the relationship between them.
Common mistakes to avoid
Several errors come up often when teams begin logging these measures. The first is mixing deal stages. Open pipeline and closed deals answer different questions, and a median computed across both can describe neither. Choose one basis for each figure and label it clearly.
The second is letting temporary values in. A deal entered at a default amount, repeated across several records, can become the mode and mislead the room. Check the most common values before you accept the mode.
The third is calculating from a segment with very few deals. A segment average built from two deals is not a reliable guide to the segment. Log the count, and if the count is small, say so on the call.
The fourth is changing the definition from one forecast to the next. A trend is only meaningful if each point was measured the same way. Keep a short definition of the basis in the same place as the figures, so the rule travels with the numbers.
The fifth is treating the three measures as a substitute for judgment. The median and mode show what the deals look like. They do not show whether a large deal will close, whether a common price will hold, or whether the pipeline will convert. Those questions still need the people closest to the accounts.
A simple routine for each forecast update
A routine helps the numbers stay consistent. Before each forecast call, export the deal list for the agreed basis. Check the list for duplicates, temporary values, currency problems, and stage mismatches. Calculate the average, median, and mode on the cleaned list. Record the count of deals and the basis beside the figures.
Then compare the three numbers with the last update. If the average moved but the median did not, look for a large deal that entered or left the list. If the mode changed, check whether a price point is shifting or whether the change reflects only a few records. Write one sentence for each notable change, so the call starts with explanations rather than surprises.
Finally, keep the log. Over several quarters, the average, median, and mode together show how the deal mix is changing. A rising median with a flat average can mean the typical deal is growing while a few large deals are shrinking. A falling mode can mean buyers are moving toward a smaller package. These patterns are hard to see in one average, and they are exactly what a forecast needs to explain.
Today, take the deal list for your next forecast call and write down its inclusion rule before calculating the figures.
Related guides: AVERAGEIFS errors that skew average deal size: 4 checks · Multi-year deal billing: 6 figures from GitLab and Stripe · Catalog price before the software deal total: 2 checks.
Common questions
Why keep the average?
The average deal size is a useful number, but it is only one view of the pipeline. With those three figures, and a clear note of what they cover, a revenue leader can check the number they quote against the deals behind it.
Does a close average and median mean similar deal sizes?
No; check the spread of the deal sizes before drawing that conclusion.