Practice by topic / Excel / Ranking & Statistics
Ranking, percentages and weighted averages answer the questions managers actually ask: who's top, how much did it change, what's the real average. These 10 exercises build up step by step, starting with calculate a weighted course grade, calculate a weighted average price, calculate a weighted customer satisfaction score.
3 of 10 free · no sign-up needed · graded on the formula you write
A1:A3 hold assignment scores, B1:B3 hold their weights (decimals summing to 1). Write a formula in C1 for the weighted final grade.
| A | B | C | |
|---|---|---|---|
| 1 | 90 | 0.5 | ? |
| 2 | 85 | 0.3 | |
| 3 | 70 | 0.2 |
Use =SUMPRODUCT(A1:A3,B1:B3).
Right — since the weights already sum to 1, SUMPRODUCT directly gives the weighted average.
Expected result in C1: 84.5
A1:A3 hold prices, B1:B3 hold quantities purchased at each price. Write a formula in C1 for the quantity-weighted average price.
| A | B | C | |
|---|---|---|---|
| 1 | 10 | 5 | ? |
| 2 | 12 | 3 | |
| 3 | 8 | 2 |
Divide the SUMPRODUCT by the total weight: =SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3)
Good — dividing the weighted sum by the total quantity gives a true weighted average price.
Expected result in C1: 10.2
A1:A3 hold satisfaction ratings from three regions, B1:B3 hold the number of respondents in each. Write a formula in C1 for the respondent-weighted average rating.
| A | B | C | |
|---|---|---|---|
| 1 | 4 | 100 | ? |
| 2 | 5 | 50 | |
| 3 | 3 | 30 |
Use =SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3)
Right — weighting by respondent count avoids letting a small region skew the overall average equally with a large one.
Expected result in C1: 4.111111111111111
A1:A3 hold investment returns (decimals), B1:B3 hold the portion of the portfolio in each (decimals summing to 1). Write a formula in C1 for the weighted portfolio return.
| A | B | C | |
|---|---|---|---|
| 1 | 0.08 | 0.5 | ? |
| 2 | 0.05 | 0.3 | |
| 3 | 0.12 | 0.2 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A3 hold three exam scores, B1:B3 hold their weights (decimals summing to 1). Write a formula in C1 for the final weighted score.
| A | B | C | |
|---|---|---|---|
| 1 | 75 | 0.2 | ? |
| 2 | 88 | 0.3 | |
| 3 | 95 | 0.5 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A3 hold delivery times from three carriers, B1:B3 hold the number of shipments each handled. Write a formula in C1 for the shipment-weighted average delivery time.
| A | B | C | |
|---|---|---|---|
| 1 | 2 | 40 | ? |
| 2 | 4 | 10 | |
| 3 | 3 | 20 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A3 hold grade points, B1:B3 hold credit hours for three courses. Write a formula in C1 for the credit-weighted GPA.
| A | B | C | |
|---|---|---|---|
| 1 | 4 | 3 | ? |
| 2 | 3 | 4 | |
| 3 | 3.7 | 3 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A3 hold conversion rates (decimals) per channel, B1:B3 hold visitor counts per channel. Write a formula in C1 for the visitor-weighted overall conversion rate.
| A | B | C | |
|---|---|---|---|
| 1 | 0.05 | 1000 | ? |
| 2 | 0.1 | 200 | |
| 3 | 0.02 | 500 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A3 hold interest rates (decimals) on three loans, B1:B3 hold each loan's balance. Write a formula in C1 for the balance-weighted average interest rate.
| A | B | C | |
|---|---|---|---|
| 1 | 0.04 | 10000 | ? |
| 2 | 0.06 | 5000 | |
| 3 | 0.03 | 15000 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A3 hold vendor quality scores, B1:B3 hold the number of orders placed with each vendor. Write a formula in C1 for the order-weighted average quality score.
| A | B | C | |
|---|---|---|---|
| 1 | 4 | 20 | ? |
| 2 | 5 | 5 | |
| 3 | 3 | 15 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →