1) Database Construction for a Pie Chart
A survey of 150 pupils of their favourite subject.
Rearrange the data into a suitable data block and use the Chart Wizard to construct a pie chart (with % and title).
Subject Maths English Science Art Drama History ICT
Frequency 24 25 22 31 19 12 17
2) Use the function button + statistical functions, etc. to enter
corresponding values in cells F6:F13 for the data in A1:C25
Use Excel to enter and calculate the data in cells F6-F13. State the Excel function and/or formula used in each calculation.
3) Consider a binomial distribution involving 12 trials with a success rate of 65%
i) Using Excel's Binomial Random number distribution Data Analysis tool, set up in A1: A120 a binomial distribution of 120 random numbers with a p-value= 0.65 and number of trials= 12
ii) Workout the theoretical mean and variance of this distribution and enter these values in cells I5 and I6 respectively. Also check with excel, the mean and variance of data in A1:A120. Enter these values in I9, I10 and compare with I5,I6.
iii) Using Excel's Histogram Data Analysis tool, make a frequency table (in D1:E15) of the data in A1:A120. Calculate the corresponding theoretical frequencies for this binomial distribution in column F. Explain the Excel formula and methods you have used to do this.
iv) Use the chart wizard to construct a bar+line chart to compare the frequencies of the random and theoretical distributions.
Please find attached question and answer sheet.
Full workings where necessary must be shown.
Demonstrates the use of Excel worksheet functions, chart wizard, Data Analysis tool for Pie Chart, Descriptive statistics, Binomial Distribution. Includes: finished spreadsheet and pie-charts; calculations of specific cell data (including functions/formulas used for each calculation); bionomial distribution analysis etc.