The Ashby Valley Schools Trust's tender sheet ranked Northgate's cleaning bid first at 9.60. It should have come last. One formula had scored price and mobilisation time as if a bigger number were a better bid.
| Cleaning tender, autumn 2026 | Figure |
|---|---|
| Bids scored | 5 |
| Northgate's score as sent | 9.60, first |
| Northgate's score, fixed | 7.43, last |
| Cleanline's score as sent | 6.93, fourth |
| Cleanline's score, fixed | 9.10, first |
| Extra cost of the recommended bid | £130,000 a year |
Scope.
SUMPRODUCT,MIN,MAX,SUM,IF,ROUND,COUNTIFSandRANK.EQwork in every version of Excel since 2010, on Windows, Mac and the web.Throughout, the bids are in A1:E6: supplier in A, annual bid in pounds in B, quality mark out of 10 in C, mobilisation in weeks in D, references mark out of 10 in E. The weights sit in B7:E7: 40%, 30%, 20% and 10%.
Each criterion is converted to a score out of 10 in K2:N6, in the same order as the weights: price in K, quality in L, mobilisation in M, references in N.
01The Sheet Ranked the Dearest Bid First
The procurement officer built the score block once. Quality and references arrived as panel marks out of 10, so L and N copied them straight across. Price and mobilisation are in pounds and weeks, so K and M converted each one to a share of the largest:
=B2/MAX($B$2:$B$6)*10
=D2/MAX($D$2:$D$6)*10
Column F weighted the four scores with =SUMPRODUCT($B$7:$E$7,K2:N2) and column G ranked them with =RANK.EQ(F2,$F$2:$F$6). Northgate came top with 9.60.
Five Bids, Four Weights, the Dearest One First
The Ashby Valley Schools Trust's cleaning tender sheet, in A1:I7. Column B holds each annual bid in pounds, C the quality mark out of 10, D the weeks to mobilise and E the references mark out of 10. Row 7 holds the weights. Columns F and G are the weighted score and rank as sent, with price and mobilisation scored as x/MAX; H and I are the same with both scored as MIN over the value.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Northgate's bid was the dearest at £490,000 a year and the slowest to start, at nine weeks. Cleanline was £130,000 cheaper and could start in four. It came fourth, asked for a debrief, and found it had scored 7.35 out of 10 on price.
02x/MAX Gives the Top Mark to the Biggest Number
Dividing by the maximum gives 10 to whichever value is largest. That is right for a quality mark, where more is better. For a price or a lead time, the largest value is the worst bid, and it got the best score.
| Supplier | Annual bid | Price score, x/MAX | Price score, MIN/x |
|---|---|---|---|
| Cleanline | £360,000 | 7.35 | 10.00 |
| Sparkle | £375,000 | 7.65 | 9.60 |
| Tidewell | £405,000 | 8.27 | 8.89 |
| Brightway | £420,000 | 8.57 | 8.57 |
| Northgate | £490,000 | 10.00 | 7.35 |
Mobilisation went the same way. Cleanline's four weeks scored 4.44 and Northgate's nine scored 10.00. Together the two columns carry 60% of the weight, so the order of the whole table turned over.
Scenario: Put the five bids in A2:E6 and enter =B2/MAX($B$2:$B$6)*10 in K2, filled down. Check K3 for Cleanline reads 7.35 and K4 for Northgate reads 10.00.
03Score Lower-Is-Better as MIN Over the Value
Turn the fraction over. The lowest value goes on top, so the cheapest bid scores 10 and every other bid scores in proportion to how close it comes:
=MIN($B$2:$B$6)/B2*10
=MIN($D$2:$D$6)/D2*10
A bid twice the lowest price scores 5. Cleanline's four weeks now score 10.00 and Northgate's nine score 4.44. Quality and references keep their marks, and the totals change places.
| Supplier | Score as sent | Rank as sent | Score fixed | Rank fixed |
|---|---|---|---|---|
| Cleanline | 6.93 | 4 | 9.10 | 1 |
| Sparkle | 6.57 | 5 | 7.84 | 2 |
| Tidewell | 8.28 | 2 | 7.76 | 3 |
| Brightway | 7.66 | 3 | 7.66 | 4 |
| Northgate | 9.60 | 1 | 7.43 | 5 |
Read the tender rules first. Some tenders specify the price formula, and the published one is the one you must use. Another common version,
=(MAX($B$2:$B$6)-B2)/(MAX($B$2:$B$6)-MIN($B$2:$B$6))*10, gives the dearest bid 0 and stretches small price gaps, so it can change the winner on its own.
Scenario: Replace K2 with =MIN($B$2:$B$6)/B2*10 and M2 with =MIN($D$2:$D$6)/D2*10, filled down. Check F3 for Cleanline now reads 9.10 and G3 reads 1.
04Test That the Best Value Scores Highest
A one-cell check catches the direction before anyone reads the ranking. In a lower-is-better column, the row with the lowest value must hold the top score. SUMPRODUCT can fetch that score without a lookup:
=SUMPRODUCT(($B$2:$B$6=MIN($B$2:$B$6))*K2:K6)
As sent it returns 7.35, which is not the top price score. After the fix it returns 10. Write the same test for mobilisation against column M, and for quality against MAX instead of MIN.
Scenario: Enter =SUMPRODUCT(($B$2:$B$6=MIN($B$2:$B$6))*K2:K6)=MAX(K2:K6) in K8. Check it reads FALSE with the old formula and TRUE with the fixed one.
05The One Row That Checked Out
The finance lead did check the sheet. They picked Brightway, the bid in the middle, and it agreed both ways. That was bad luck with a mathematical cause.
The two formulas give the same score when the value sits at the geometric mean of the lowest and highest. Brightway's £420,000 is exactly that for £360,000 and £490,000, and its six weeks is exactly that for four and nine.
| Brightway | x/MAX | MIN/x |
|---|---|---|
| Price score | 8.57 | 8.57 |
| Mobilisation score | 6.67 | 6.67 |
| Total | 7.66 | 7.66 |
Spot-check the extremes instead. The cheapest and the dearest bids are the rows a direction mistake moves most, which is why the test in section 4 looks at the minimum.
Scenario: Enter =B2/MAX($B$2:$B$6)*10 in P2 and =MIN($B$2:$B$6)/B2*10 in Q2, filled down. Check the two columns agree only on row 2, then compare rows 3 and 4.
06Check the Weights Add Up to 100%
SUMPRODUCT will multiply by whatever weights it is given. If they add up to 110%, every score is inflated and none of them errors. Someone adds a 10% sustainability criterion and forgets to take it from another.
Make the total refuse to calculate when the weights are wrong:
=IF(ROUND(SUM($B$7:$E$7),6)<>1,"Weights ≠ 100%",SUMPRODUCT($B$7:$E$7,K2:N2))
The ROUND matters. Percentages are stored as binary fractions, so a correct set of weights can add up to a hair under 1. A plain <>1 could then reject weights that are right.
Scenario: Enter the guarded formula in F2 and fill it down. Change C7 from 30% to 40% and check every score turns into the warning, then set it back.
Try it in the grid07Keep the Scores in the Weights' Order
SUMPRODUCT pairs the first weight with the first score, the second with the second, and so on. It does not read the headers. If the score block puts quality before price, quality silently takes the 40%.
| Weight row | Score block, as intended | Score block, columns swapped |
|---|---|---|
| 40% | Price | Quality |
| 30% | Quality | Price |
| 20% | Mobilisation | Mobilisation |
| 10% | References | References |
Lay the score block out in exactly the order of the weight row, with the same headings above both. Then a column inserted on one side and not the other is visible at a glance.
Scenario: Swap the contents of columns K and L, so quality comes first. Check that F4 for Northgate moves from 7.43 to 7.59 with no error anywhere, then swap them back.
Try it in the grid08Rank on the Full Score, Not the Rounded One
RANK.EQ gives tied scores the same rank and skips the next one. Round the scores first and you can create a tie that was never there. Rounded to one decimal, Sparkle's 7.84 and Tidewell's 7.76 both read 7.8.
| Supplier | Score fixed | Rounded to 1 dp | Rank of rounded |
|---|---|---|---|
| Sparkle | 7.84 | 7.8 | 2 |
| Tidewell | 7.76 | 7.8 | 2 |
| Brightway | 7.66 | 7.7 | 4 |
Rank the unrounded score in column F and only format it to two decimals. If two bids really do tie, break it with a rule the tender states, such as the lower price, rather than with row order.
For a unique rank that falls back to row order, =RANK.EQ(F2,$F$2:$F$6)+COUNTIFS($F$2:F2,F2)-1 works. Use it only where the rules allow it.
09Five Traps
- x/MAX on a cost. It gives the top mark to the highest price or the longest lead time.
- Checking the middle row. A value at the geometric mean scores the same both ways.
- Weights that do not add up.
SUMPRODUCTmultiplies by 110% without complaint. - A score block in a different order.
SUMPRODUCTpairs by position, not by heading. - Ranking rounded scores. One decimal can create a tie that the full scores do not have.
10Four Exercises
- Reproduce it. Score the five bids with x/MAX and confirm Northgate comes first at 9.60.
- Fix it. Rewrite price and mobilisation as MIN over the value and confirm Cleanline leads at 9.10.
- Test it. Add the section 4 check for both lower-is-better columns.
- Guard it. Add the weight check and the unrounded rank, then change one weight and watch the warning appear.
What to Take Away
Every bid on the trust's sheet was typed correctly, and so was every weight. One formula, filled across, assumed that more is always better, and two of the four criteria are the opposite.
Before you weight anything, write down which way each criterion runs. Then check that the cheapest bid has the top price score before anyone reads the ranking.