Advanced Excel: Statistical Formulae and Functions

Excel Statistical Formulae and Functions - Exercises

Advanced Excel: Statistical Formulae and Functions

Exercises

The files required for these exercises are in r:\training-dir\{Monday|Tuesday|Thursday}\4-adv-excel-stats

1) Simple Calculations and Descriptives

Using results.xls

· Find the mean exam score for each subject (ie English, History, Maths);

· Find the median exam score in each subject;

· Find the modal exam score in each subject.

Using medicaltrialX.xls

· What is the average score for hbefore for men?
(There are at least two ways todo this:

1. Use sumif and countif

2. Filter the data (look at the data menu) and sort by gender before doing the calculations

Either method is fine for this task.)

2) Recoding and Computing

· Using medicaltrialX.xls Compute a new variable dh (non-destructively) which expresses the difference in hormone saturation levels before and after treatment;

· Recode income into a discreet variable of three income bands: low – below 30000, medium – below 50000 and high – more than 50000;

· Using results.xls compute a new maths score weighted by .20 and use this to compute a new mean exam score.

· OPTIONAL: Using santa.xls, identify the missing values in the data set. Recode the missing values

3) Dispersion and Frequency

Using medicaltrialX.xls

· What is the range of hbefore?

· What is the range of dh

· What is the variance of hafter?

· What are the standard deviations of hbefore, hafter and dh?

Using results.xls

· What is the most frequent average exam score?

4) Frequencies

Using the file results.xls

· Recode average exam scores into

· Stream A: scores of 60 and above

· Stream B: scores of 50 and above

· Stream C: scores below 50

· Determine how many pupils are in each stream.

5) Association

Using medicaltrialX.xls

· Determine if there is a correlation between age and dh

o For men

o For women

· Determine if there is a correlation between income and dh

Using results.xls

· Determine if there is any trend in the relationship between scores in

o English and History

o English and Maths

6) Comparing two means - Optional

If you know how to interpret a student t-test, you can do this exercise using the appropriate function from the AnalysisToolpak.

Using medicaltrialX.xls

· Determine if there is a significant difference between dh scores of men and women

· Determine if there is a significant difference between hbefore and hafter scores for men

7) Goal Seek

1. Open the workbook Goal Seek exercise.xls. This shows 2 months of trading figures.

2. Due to industrial action, the figures for the month of August need changing. Change Sales, Salary and Costs for August to 0.

3. The business is making a loss of £620 for the year (cell E21). That can be altered by changing the price charged in September. The question is “How much do we need to charge in September to break even at the end of the year?”.

4. Using the Goal Seek feature, set the total in cell E21 to 0 by changing the price charged in September.

5. The value found for the cost of goods in September is £6.23. Click in cell D5. The actual value found is 6.2296296… as shown in the Formula bar. You can see that Goal Seek is very accurate.

6. Save the workbook as Goals and close it.

8) Solver

1. Open the workbook Solver exercise.xls. This spreadsheet contains a table to work out the optimum price to charge for goods. We need to maximise sales and minimise costs.

2. Note the profit when the price is set to £1.

3. Change cell D2 to a price of £3. The profit will increase.

4. Change the price to £9. The profit has decreased as the number of sales has dropped.

5. Using the Solver, find the optimum profit in D7 (Ensure the Max button is selected.) by changing the price in D2. Do not close the Solver Results dialog box yet.

6. A price of £5.30 should have been returned. Save the scenario as Price, and then accept the solution.

7. Save the workbook as Solver Answer.

8. As the company can only produce 500 items, a constraint must be added to restrict the sales to 500.

9. Using the Solver, add a suitable constraint to cell D3. The new price should be £6.88.

10. Save this scenario as Restrict Sales and then accept the solution.

11. Save the workbook as Solver Answer 2.

12. View the Price scenario.

13. View the Restrict Sales figures again (click on Restrict Sales then the Show button).

14. Close the Scenario Manager dialog box.

15. Close the workbook, saving the changes when prompted.

16. Open the sample file SOLVSAMP.XLS from the examples folder. Familiarise yourself with it’s contents. Close the file when done.

9) Regression

Imagine that you are a University admissions tutor for the Department of History. A level results for this years History exams have been lost! Investigate the possibility of reliably estimating a candidates History result from the results you have in results.xls. Could you reliably predict likely History results in any way?

Create a scatterplot with trend line and error bars. The error bars should show plus or minus one standard deviation.