Using Excel for Statistics: Assignment 2 Guide and Formulas

This document serves as a comprehensive Excel guide tailored for a statistics assignment. It provides detailed instructions and links to external resources on various Excel functionalities crucial for statistical analysis, including moving and copying worksheets, inserting and deleting worksheets, selecting, copying, and pasting data, sorting and filtering data, and using tabs. The guide offers insights into basic formulas, fill-down features, and autosum functionalities. It also covers essential statistical calculations like mean, median, mode, correlation coefficient, standard deviation (population), quartiles, and includes guidance on creating boxplots, pie charts, scatterplots, and trend lines. The document also offers instructions on renaming worksheet tabs, copying cells, sorting, filtering data, and utilizing basic formulas such as calculating correlation coefficients and population standard deviation, providing a complete toolkit for students to effectively tackle their statistics assignments using Excel. Students can find additional resources, including past papers and solved assignments, on the Desklib platform.

Applying Mathematics 0003
Excel Guide to help with Assignment 2
Worksheets How to move and copy worksheets or worksheet data
https://support.office.com/en-us/article/move-or-copy-worksheets-or-
worksheet-data-47207967-bbb2-4e95-9b5c-3c174aa69328
Insert or delete a
worksheet
How to insert or delete a worksheet
https://support.office.com/en-ie/article/insert-or-delete-a-worksheet-
19d3d21e-a3b3-4e13-a422-d1f43f1faaf2
Select, copy and paste How to select, copy and paste data
https://support.office.com/en-ie/article/move-or-copy-cells-and-cell-
contents-803d65eb-6a3e-4534-8c6f-ff12d1c4139e
Sort Data How to sort data in a table by text (A-Z or Z-A) or by number (smallest
to largest or largest to smallest)
https://support.office.com/en-us/article/sort-data-in-a-range-or-table-
62d0b95d-2a90-4610-a6ae-2e545c4a4654
Filter data How to filter data according to top 10, between a range …
https://support.office.com/en-us/article/filter-data-in-a-range-or-
table-01832226-31b5-4568-8806-38c37dcc180e
Tabs How to use Tabs
https://support.office.com/en-ie/article/where-are-my-worksheet-
tabs-42623d50-2f9b-4583-ad9c-f1b381185e48
Basic Formulas How to perform basic calculations with formulae
https://support.office.com/en-us/article/overview-of-formulas-in-
excel-ecfdc708-9162-49e8-b993-c311f47ca173
Fill Down How to fill down (quickly copy formulas into other cells)
https://support.office.com/en-gb/article/fill-a-formula-down-into-
adjacent-cells-041edfe2-05bc-40e6-b933-ef48c3f308c6
Autosum How to automatically sum numbers
https://support.office.com/en-ie/article/use-autosum-to-sum-
numbers-543941e7-e783-44ef-8317-7d1bb85fe706
Calculator How to do basic calculations such as add, subtract, multiply, divide and
% in Excel
https://support.office.com/en-ie/article/use-excel-as-your-calculator-
a1abc057-ed11-443a-a635-68216555ad0a
Mean How to calculate the mean of a group of numbers
https://support.office.com/en-us/article/calculate-the-average-of-a-
group-of-numbers-e158ef61-421c-4839-8290-34d7b1e68283

Median How to calculate the median of a group of numbers
https://support.office.com/en-us/article/calculate-the-median-of-a-
group-of-numbers-2e3ec1aa-5046-4b4b-bfc4-4266ecf39bf9
Mode How to calculate the mode of a group of numbers
https://support.office.com/en-us/article/mode-function-e45192ce-
9122-4980-82ed-4bdc34973120
Correlation Coefficient How to calculate the correlation coefficient between two sets of
numbers
https://support.office.com/en-ie/article/correl-function-995dcef7-
0c0a-4bed-a3fb-239d7b68ca92
Standard Deviation
(Population)
How to calculate the standard deviation (population) of a group of
numbers.
https://support.office.com/en-ie/article/stdev-function-51fecaaa-
231e-4bbb-9230-33650a72c9b0
Quartile 1, Quartile 2
(median) and Quartile 3
How to calculate the Quartile 1, Quartile 2 and Quartile 3 for a group
of numbers
https://support.office.com/en-ie/article/quartile-function-93cf8f62-
60cd-4fdb-8a92-8451041e1a2a
Boxplots How to draw a boxplot (newer 2016 versions)
https://support.office.com/en-us/article/create-a-box-and-whisker-
chart-62f4219f-db4b-4754-aca8-4743f6190f0d
How to draw a boxplot (Excel 2013)
https://support.office.com/en-gb/article/create-a-box-plot-10204530-
8cdf-40fe-a711-2eb9785e510f
Pie chart How to draw a pie chart
https://support.office.com/en-us/article/add-a-pie-chart-1a5f08ae-
ba40-46f2-9ed0-ff84873b7863
*** Categories need a “Non- numeric” title
Eg “4 rooms” not “4”
Scatterplots Instructions to create a scatterplot in Excel can be found at:
https://support.office.com/en-ie/article/present-your-data-in-a-
scatter-chart-or-a-line-chart-4570a80f-599a-4d6b-a155-104a9018b86e
*** Look only at how to present tour data in a scatterplot
Trend line How to draw a trend line on a scatterplot
https://support.office.com/en-ie/article/add-a-trend-or-moving-
average-line-to-a-chart-fa59f86c-5852-4b68-a6d4-901a745842ad
Equation of a Trend line How to display the equation of a trend line on the scatterplot
https://www.online-tech-tips.com/ms-office-tips/add-a-linear-
regression-trendline-to-an-excel-scatter-plot/

 You can select instructions for mac or earlier versions of Excel near the top of each of these
pages:
Some Useful Excel Instructions
1. Rename worksheet tab by double-clicking the tab
One way to rename a worksheet tab in Excel is by double-clicking on the sheet tab. When you double-click on the
worksheet tab at the bottom, the tab name (e.g. Sheet1) is highlighted. After that, you just have to type the new
name.
2. Copy cells by using Copy and Paste
Select a cell or a cell range.
Select Home > COPY or press Ctrl + C.
Select a cell where you want to move the data.
Select Home > Paste or press Ctrl + V.

3. Sorting Data
To sort a range of data:
1. Select the cell range you want to sort. ...
2. Select the Data tab on the Ribbon, then click the Sort command.
3. The Sort dialog box will appear. ...
4. Decide the sorting order (either ascending or descending). ...
5. Once you're satisfied with your selection, click OK.
6. The cell range will be sorted by the selected column.
In this example we sorted the “Score after” into ascending order.

4. Filtering Data
Filter a range of data
1. Select any cell within the range.
2. Select Data > Filter.
3. Select the column header arrow .
4. Select Text Filters or Number Filters, and then select a comparison, like Between.
5. Enter the filter criteria and select OK.
In this example we filtered to only retain “Score after” between 20 and 40 inclusive.
6. How to use Autosum
Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done.
When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers
The Example shows Column C Autosum formula

7. How to use Basic Formulas
Yu can type a formula in any cell starting with a =
This formula below compares the score after to the score before as a percentage
Y
8. Fill formulas into adjacent cells
1. Select the cell with the formula and the adjacent cells you want to fill.
2. Click Home > Fill, and choose either Down, Right, Up, or Left.
Or You can quickly copy formulas into adjacent cells by using the fill handle.
When you drag the handle to other cells, Excel will show different results for each cell.