Fdaytalk Homework Help: Questions and Answers: What formula might you write and place in an empty cell to calculate the average sales per sales rep for the entire company last year?
a) = MAX(B4: B8)/SUM(C4: C8)
b) = SUM(B4: B8)/SUM(C4: C8)
c) = AVERAGE(B4: B8)/SUM(C4: C8)
d) = SUM(B4: B8)/MIN(MAX(C4: C8))
Answer
To determine the average sales per sales rep for the entire company last year using the given spreadsheet, we need to use the appropriate formula.
Given Data:
Last Year’s Sales
| Region | Sales ($m) | Sales Reps | Sales per Rep ($m) |
|---|---|---|---|
| Northeast | 52.1 | 14 | 3.7 |
| Southeast | 40.7 | 3 | 13.6 |
| Central | 23.2 | 27 | 0.9 |
| Southwest | 53.1 | 12 | 4.4 |
Calculating the Total Sales and Total Sales Reps:
- Total Sales: 52.1 + 40.7 + 23.2 + 53.1=169.1
- Total Sales Reps: 14 + 3 + 27 + 12=56
Average Sales per Sales Rep:
- Average Sales per Rep = Total Sales / Total Sales Reps = 169.1/56 ≈ 3.02
Now let’s analysis each given option: Step by step
a) =MAX(B4:B8)/SUM(C4:C8)
- This formula calculates the maximum sales value in the range B4 and divides it by the sum of the sales reps in the range C4.
- max(52.1, 40.7, 23.2, 53.1) / (14+3+27+12) = 53.1/56 ≈ 0.95
- This is incorrect because it uses MAX instead of SUM for sales. It would divide the highest sales value by the total number of reps, which doesn’t give the average for the entire company.
b) =SUM(B4:B8)/SUM(C4:C8)
- This formula calculates the total sales in the range B4 and divides it by the sum of the sales reps in the range C4.
- (52.1 + 40.7 + 23.2 + 53.1) / (14 + 3 + 27 + 12) = 169.1/56 ≈ 3.02
- This is correct because it correctly calculates the average sales per sales rep by using the total sales and total sales reps.
c) =AVERAGE(B4:B8)/SUM(C4:C8)
- This formula calculates the average sales value in the range B4 and divides it by the sum of the sales reps in the range C4.
- average(52.1, 40.7, 23.2, 53.1) / (14 + 3 + 27 + 12) = (169.1/4)/56 ≈ 42.275/56 ≈ 0.75
- This is incorrect because it uses AVERAGE for sales instead of SUM. It would divide the average sales value by the total number of reps, which is not the correct calculation for overall average sales per rep.
d) =SUM(B4:B8)/MIN(MAX(C4:C8))
- This formula calculates the total sales in the range B4 and divides it by the minimum of the maximum value in the range C4.
- ∑(52.1, 40.7, 23.2, 53.1) / min(max(14, 3, 27, 12)) = 169.1 / min(27) = 169.1/27 ≈ 6.26
- This is incorrect because it does not correctly calculate the average sales per sales rep.
- This is incorrect beacuse It uses MIN(MAX(C4:C8)) in the denominator, which would just give the largest number of reps in any single region.
- Even It doesn’t consider all sales reps, only the maximum from one region.
Final Answer
Based the above analysis, the correct answer is:
b) =SUM(B4:B8)/SUM(C4:C8)
Learn More: Fdaytalk Homework Help
Q. A researcher is using a generative AI tool to find out about an event that happened two days ago
