Course: Microsoft Excel (ACCO1095) (Year 2 Students)

Answers for the Analysing Data Lab Exercise

1. How many sales were there for Helmets?

Answer: 3,794
Formula: =COUNTIF(B:B,"Helmets")

2. What was the total value of sales for Mountain Bikes?

Answer: £3,687,291.06
Formula: =SUMIF(B:B,"Mountain Bikes",D:D)

3. What was the average value for a sale in the Tires and Tubes category?

Answer: £15.33
Formula: =AVERAGEIF(B:B,"Tires and Tubes",D:D)
or, for earlier versions of Excel which do not have the AVERAGEIF function...
Formula: =SUMIF(B:B,"Tires and Tubes",D:D)/COUNTIF(B:B,"Tires and Tubes")

4. How many Water Bottles were sold?

Answer: 2,502
Formula: =COUNTIF(C:C,"Water Bottle")

5. What was the total value of clothing sales (Jerseys, Shorts and Caps)?

Answer: £142,236.33
Formula: =SUMIF(B:B,"Jerseys",D:D)+SUMIF(B:B,"Shorts",D:D)+SUMIF(B:B,"Caps",D:D)

6. How many people had more than two cars?

Answer: 5
Formula: =COUNTIF(I:I,">2")

7. What was the average age of the people in the survey?

Answer: 44.8
Formula: =AVERAGE(J:J)

8. What is the average income of the female customers?

Answer: £53,076.92
Formula: =AVERAGEIF(C:C,"Female",D:D)
or, for earlier versions of Excel which do not have the AVERAGEIF function...
Formula: =SUMIF(C:C,"Female",D:D)/COUNTIF(C:C,"Female")

9. What percentage of customers surveyed were over 45 years old?

Answer: 53%
Formula: COUNTIF(J:J,">45")/COUNT(J:J)

10. How many customers are home-owners?

Answer: 58
Formula: =COUNTIF(H:H,"Yes")

You can download a copy of the exercise file containing the completed formulas here:
Excel 2007 version: Sales with Answers_xlsx.zip
Excel 2003 version: Sales with Answers_xls.zip

[Updated: 03/11/2009]