151Tutorial-1-Excel-Primer-Function-Average-Median _1_ Sp24

Yellow highlights mark locations referred to in boxes. Version 1.02 10/6/2015 Minor typo fixed In this tutorial, information is presented in blue boxes. An Excel file usually consists of multiple pages (called Worksheets ) that can be accessed by clicking on tabs that appear at the bottom of the window. Instructions are in purple boxes. Click on the worksheet tabs from left to right to follow the tutorials on each page. Exercises are presented in red boxes. You can also use Excel's built-in help function by pressing F1. Depending on the Excel version and configuration, this may require an internet connection. This tutorial was developed in Excel 2010. Not all features and functions may work the same way in earlier or later versions of Excel.

Data This Cell is E8. Each cell has an address based on the row and column. This is column E. At the bottom, you see tabs for different pages (called Worksheets). Click on different tabs to look at other pages. This is an Excel Worksheet. It's basically a big table. Each cell can contain data like text and numbers. You can click on a cell to select it, or use arrow keys to move around the sheet. The space above the column hea the right of " fx", is the formula b the selected cell is displayed her

Plant No. 1 2 3 4 5 6 7 8 Height (cm 7.5 10.1 8.3 9.8 5.7 10.3 9.2 8.7 Plant NumHeight (cm) Plant NumHeight (cm 1 7.5 1 7.5 2 10.1 2 10.1 3 8.3 3 8.3 4 9.8 4 9.8 5 5.7 5 5.7 6 10.3 6 10.3 7 9.2 7 9.2 8 8.7 8 8.7 1. When you enter data in Excel, think about how you'd like it organized. Do you want to display it horizontally like this? 2. Or vertically like this? 3. You can work either way, but it's usually easier to manage the data if you enter them vertically.

Color yellow green yellow green yellow green green yellow 4. If your data has more than one attribute, you can add another column.

3.141593 ny formatting tools to make ell show the first 3 ficant digits of Pi , 3.14.

Plant Number Height (cm) 1 7.7 2 11.1 3 8.4 4 9.8 5 5.7 6 10.3 7 9.2 8 8.8 Mean using arithmetic formulae Count 8 Sum 71 Mean 8.875 Plant Number Height (cm) 1 7.7 2 11.1 3 8.4 A cell entry that starts with an equal sign (=) is a Formula . Formulae can have numbers or cell addresses. For example (see K4 to K6), you can have =A2/5 (divide the content of A2 by 5) =A2+A3 (add the contents of A2 and A3) =8/3 (8 divided by 3) So Excel can be used as a calculator. Mathematical operators supported: + addition; - subtraction; * multiplication; / division; ^ exponentiation Arithmetic mean (in cell B14) is calculated by adding up all the values (in B13) and dividing by the count (in B12). Click on B13 and B14 to see what formulae they contain in the formula bar above. When the formula is displayed, click inside the formula bar to see which cells are relevant in the formula. Press "Esc" key to return to the Worksheet. Exc op + a - su * m / d ^ e The exp add Exa =1+ You Exa =(1 Click on B29 and type "=" to calculate the sum of all the plant heights in B26 to B28. (Add all the values together.) In the formula, use cell references (B26, B27, etc.) and not the actual values (7.7, 11.1, etc.) You can either type all of the formula, or click on each cell and type the addition symbol after each click. Hint: The formula in B13 is very similar to the one you want.

Plant Number Height (cm) 1 7.5 2 10.1 3 8.3 4 9.8 5 5.7 6 10.3 7 9.2 8 8.7 Mean using arithmetic formulae Sum 69.6 Count 8 Mean 8.7 Mean using functions Sum 69.6 Count 8 Mean 8.7 Mean using the average function Mean 8.7 79.6 Excel has a powerful set of Functions that can be used in a cell. Each function has a name and an argument list in parentheses . An argument is basically the input to the function. B17 uses the SUM function that adds all entries in the argument list separated by commas. The list can contain actual numbers or references to a cell. Click to see the formula. B18 uses the COUNT function that counts the number of entries in a list. Click to see the formula. B22 uses the AVERAGE function that calculates the mean of the entries in a list. Click to see the formula. You can insert a function by selecting a cell then clicking on the fx to the left of the formula bar. Click on D8 and click on fx to see a list of functions available. B28 is an example in which the function has both a cell reference and a number. The formula adds 10 to the content of cell B17. Click to see the formula.

179 9429 3362 Enter a formula in L7 with a function that calculates the sum of values in J6, K3, and L4.

Numbers 1 2 3 4 5 6 7 8 9 10 Sum Exercise: In cell K13, use a function to add up the numbers highlighted in column K. 1. Click on K13 2. Type "=sum(" 3. Click and drag K2 to K11. (Click on K2, then without letting go, drag to K11.) 4. Type ")", then Enter. K13 should show 55. If not, click on K13, then look at the formula bar and make sure it shows "=SUM(K2:K11)" If not, edit the formula directly in the formula bar to correct the error.

Mouse No. Time (sec.) Sorted Time 1 31 31 2 33 33 3 163 163 4 33 33 5 28 28 6 29 29 7 33 33 8 27 27 9 27 27 10 34 30 11 35 35 12 28 28 13 32 32 Mean 41 Median 32 Here we are using the AVERAGE and MEDIAN functions. Median In Excel, it's easy to sort numbers. Select E2:E14 by click-dragging. PC Mac PC Excel 2010: Select Sort & Filter in the Home tab in the ribbon abo then select "Sort Smallest to Largest" or "Sort Largest to Smallest" a the result. Mac Excel 2011: Select the Sort icon (A->Z) on the toolbar, then sele "Ascending" or "Descending" and see the result. The Median value is the middle of the range.

Mean Beak Depth (mm) = Median Beak depth (mm)= Columns J and K contain data from 50 medium ground finches. Column J shows the ID # of a bird, and column K shows its beak depth. Using a function, calculate the mean beak depth of these birds in cell H20. The medium ground finch is a species of Darwin's finches from the Galapagos Islands. In the exercise section of these tutorials, you will manipulate a subset of experimental data provided by scientists Peter and Rosemary Grant, who have studied these birds for many years. Refer to: http://www.hhmi.org/biointeractive/ evolution-action-data-analysis for more background.

Bird ID Beak Depth (mm) 9 8.30 12 7.50 276 8.00 278 10.60 283 11.20 288 9.10 293 9.50 294 10.50 298 8.40 307 8.60 311 9.20 315 8.80 321 8.50 342 8.00 343 9.70 345 8.40 346 7.72 347 9.30 352 7.05 356 8.50 413 8.20 420 9.70 422 10.30 428 7.05 452 8.90 456 9.60 457 7.85 458 9.98 461 9.80 462 8.80 468 9.00 503 9.10 506 9.20 507 8.80 509 9.20 511 8.80 512 6.90 519 8.30 522 8.40 561 6.80 564 9.30 605 11.22 609 10.50 610 9.00