À propos de cet exercice
Un jeu de données de 50 lignes sur une feuille, 3 cellules de réponse, environ 8 minutes. Corrigé dans votre navigateur : la valeur et la formule qui la produit, avec des indications sur la correction à apporter.
Comment le résoudre
- I2
=SUMPRODUCT(E2:E51,F2:F51) - I3
=SUMPRODUCT(E2:E51,F2:F51)/SUM(E2:E51) - I6
=SUMPRODUCT((D2:D51=I5)*E2:E51,F2:F51)/SUMIF(D2:D51,I5,E2:E51)
SUMPRODUCT takes ranges of the same size, multiplies them pair by pair and adds the products, so =SUMPRODUCT(E2:E51,F2:F51) is the total spend — the same as writing a quantity × price column and summing it, in one cell. Dividing that total by the total kilograms gives the weighted average: each lot's price counts in proportion to its size, which is why the result ($6.92) is lower than the plain AVERAGE of the price column ($8.04). A test such as (D2:D51=I5) inside SUMPRODUCT turns into 1 for the matching rows and 0 for the rest, so only that origin's spend is added; SUMIF supplies the matching kilograms.
Pitfall 1: =AVERAGE(F2:F51) is only right when every lot has the same quantity; with unequal weights it leans towards the small lots — here the small, dear Kenya lots pull it up, and the volume discount does the same inside each origin (AVERAGEIF gives $8.18 for Colombia instead of $7.91). Pitfall 2: the ranges must be the same shape, otherwise SUMPRODUCT returns #VALUE!; a text cell such as "n/a" counts as 0 in the comma form (SUMPRODUCT(E,F)) but produces #VALUE! in the multiplication form (SUMPRODUCT(E*F)). Pitfall 3: a condition on its own, SUMPRODUCT(D2:D51=I5,E2:E51), multiplies TRUE/FALSE values that SUMPRODUCT treats as 0 — multiply the test by a number range ((D2:D51=I5)*E2:E51) or coerce it with -- first. Alternatives: =SUM(E2:E51*F2:F51) does the same in Excel 365 (dynamic arrays) and needs Ctrl+Shift+Enter in Excel 2019; a helper column G = E2*F2 with =SUM(G2:G51)/SUM(E2:E51) is easier to audit and what many finance teams prefer; =SUMIFS(G2:G51,D2:D51,I5)/SUMIFS(E2:E51,D2:D51,I5) is the helper-column version of the origin figure. Rounding: keep the division unrounded for the valuation and format the cell as currency; =ROUND(...,2) is accepted here because the tolerance is half a cent.
Autres méthodes valables
- =SUMPRODUCT(E2:E51*F2:F51)/SUM(E2:E51) — the multiplication form of SUMPRODUCT
- =SUMPRODUCT(--(D2:D51=I5),E2:E51,F2:F51)/SUMIF(D2:D51,I5,E2:E51) — the origin figure with the test coerced by --
- =SUMPRODUCT((D2:D51=I5)*E2:E51*F2:F51)/SUMPRODUCT((D2:D51=I5)*E2:E51) — the origin figure with SUMPRODUCT on both sides
- =SUM(E2:E51*F2:F51)/SUM(E2:E51) — array multiplication inside SUM (Excel 365); not accepted here because the task names SUMPRODUCT
Erreurs fréquentes
Using =AVERAGE(F2:F51) for the average price: it gives $8.04 instead of $6.92 because the 60 kg lots and the 840 kg lots count the same — the quantities must weight the prices.
Comment nous corrigeons
Toute formule qui produit le bon résultat est acceptée, sauf si la tâche nomme une fonction : une autre fonction rapporte alors la moitié des points. Nous comparons les valeurs avec une petite tolérance et ignorons la casse et les espaces dans le texte. Un nombre correct saisi à la main au lieu d’une formule rapporte la moitié des points. Les tâches sur plusieurs cellules rapportent tous les points avec 100 % de cellules justes, la moitié à partir de 50 %. Lorsque c’est pertinent, nous modifions aussi une cellule d’entrée sur une copie de votre feuille et vérifions que votre formule suit.
Réponses calculées selon les règles d’Excel 365 et vérifiées dans notre correcteur le 16 novembre 2026. Auteur : GridBelt team.