Useful websites:



Tips for Excel Assignment 1

Useful websites:

• For a list of hours for computer labs where you can use excel:

• The SmartPsych Excel Tutorial:

Creating formulas in Excel

• To make the contents of a cell a formula, start it with an ‘=’

• You can use numbers or cell references to represent quantities.

• You can use mathematical operators: + - * /

• You can use premade functions (example ‘=SUM(5,7,2)’)

• You can use paste functions (use the fx button and choose Statistical)

• To put a range of cell references in your formula: type the first and last cells, separated by a colon ‘:’ or just select the range of cells you want using the mouse.

Useful functions:

• To square something (A1): =power(a1,2)

• To take the square root of something (A1): =sqrt(a1)

• To add stuff: =SUM(a1:a10)

Copying and pasting functions

• When you copy and paste a function that includes a cell reference, Excel will paste a new function that is the same relatively, rather than literally. For example, if cell A10 contains the formula: ‘=SUM(A1:A9)’ and I copy and paste the contents of that cell into B10, the new contents will be ‘=SUM(B1:B9)’.

• If you want to copy the products of formulas to new cells, but you want to preserve the actual values and not recalculate the formulas based on their new relative position, you can copy and paste values only using “paste special” instead of “paste” under the edit menu, and select “values only” in the dialogue box that appears.

• You can paste a formula to a range of cells by just copying it and selecting the whole range you want to paste it to and pasting

Using the paste functions

To use the paste functions to get the mean, variance, and SD, use the paste function button (fx), select ‘Statistical’, then choose ‘AVERAGE’ for mean, ‘VARP’ for variance, and ‘STDEVP’ for standard deviation.

To create frequency polygons in excel:

1. Create your frequency distribution, making sure it starts from the lowest value at the top.

2. For the relative frequency polygon create a column for the midpoint.

3. Copy and paste data from the frequency distribution so that you have columns for the frequency of each Sample.

4. Add columns, using formulas, for the relative frequencies. Move the columns around so that the midpoint is to the left of the two relative frequency columns.

5. Select the midpoint column and the two relative frequency columns, and hit the chart button. Select “scatterplot” and follow the dialogue boxes to create your graph.

6. For the cumulative percentage polygon, copy and paste the cumulative percentage columns for the two samples to the right of the URL column.

7. Select the URL column and the two relative frequency columns, and hit the chart button. Select “scatterplot” and follow the dialogue boxes to create your graph.

Tips for good printing

➢ Start with doing a “print preview” to get an idea of where your data and graphs are falling on pages. When you close this, you will be able to see dashed lines along the column and row boundaries that mark the edge of pages. Move your data and charts around so that things don’t run from one page onto the next.

➢ When you try to enter your student number, Excel will drop the 0’s from the front unless you first change the formatting of the cell to “text”. (Format menu-> cells-> number-> text)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download