Topic 11 of 15, EHL Maths & Excel final (AP)
⚖
Weighted average
A weighted average gives more weight to the values that occur more often, for example the average room rate when more Standard than Deluxe rooms are sold.
When the exam asks for it
- “average price of all units sold”, “average rate”, “how many must be sold to reach an average of …”
Formulas
Average
AVG = (P1*Q1 + P2*Q2)/(Q1+Q2)Missing Q2
Q2 = Q1*(AVG-P1)/(P2-AVG)Missing Q1
Q1 = Q2*(P2-AVG)/(AVG-P1)Missing P2
P2 = (AVG*(Q1+Q2)-P1*Q1)/Q2Missing P1
P1 = (AVG*(Q1+Q2)-P2*Q2)/Q1In Excel
=SUMPRODUCT(prices, quantities)/SUM(quantities)Worked example
Average price. Two room types; the average price is weighted by the nights sold.
E4fx
=(A4*B4 + C4*D4)/(A4+C4)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Q1 | P1 | Q2 | P2 | AVG | |
| 2 | Standard rooms | Deluxe rooms | Average price | |||
| 3 | Weight 1 (nights) | Price 1 (CHF) | Weight 2 (nights) | Price 2 (CHF) | ||
| 4 | 30 | 120 | 10 | 200 | missing → 140 | |
| 5 | 30 | 120 | missing → 18 | 200 | 150 | |
| 6 | ||||||
Sheet1
⬇ Download practice ExcelExample, Practice and Answers sheets with working formulas. Free.
Step by step
=(A4*B4 + C4*D4)/(A4+C4)AVG = (30*120 + 10*200)/(30+10) = 5,600/40 = 140 CHF (not 160, the simple average)=A5*(E5-B5)/(D5-E5)missing Q2 for a target AVG of 150: 30*(150-120)/(200-150) = 18 Deluxe nights- Check: (30*120 + 18*200)/487,200/48 = 150 ✓
⚠ Exam traps
- The simple average (120+200)/2 = 160 is wrong when the quantities differ.
- In SUMPRODUCT both ranges must have the same size.
Practice question
A hotel sells 60 nights at 100 CHF and 40 nights at 150 CHF. What is the average rate?
Show answer
✓ (6,000+6,000)/100 = 120 CHF.
Stuck on this topic before your exam? We explain it one-to-one on your own Excel files, live online. Short crash courses before the EHL Maths & Excel final are possible on weekdays and weekends.
Book a lesson
Tell us your course, your exam date and the topics that worry you. We start with what matters most for your grade.
WhatsApp is preferred
The fastest way to book: send us a message and we usually answer the same day.
or email info@ytutor.net
Send a message
We answer by email or WhatsApp.