annotated-Lab%203%20Tutorial%201%20-%20Knudson.xlsx

pdf

School

Texas Tech University *

*We aren’t endorsed by this school

Course

1403

Subject

Statistics

Date

Jan 9, 2024

Type

pdf

Pages

6

Uploaded by JudgeMaskBadger31

Report
Yellow highlights mark locations referred to in boxes. Version 1.02 9/28/2015 Version 1.03 2/10/2016 Added explanation for using F4 button in absolute reference 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 Mac versions of Excel.
Plant Number Height (cm) Mouse No. Time (sec.) 1 7.5 1 31 2 10.1 2 33 3 8.3 3 163 4 9.8 4 33 5 5.7 5 28 6 10.3 6 29 7 9.2 Range = 136 7 33 8 8.7 8 27 9 27 Max 10.3 10 34 Min 5.7 11 35 Range 4.6 12 28 13 32 Range 4.6 The range of the data is calculated in B13 by subtracting the minimum value in B12 from the maximum value in B11. Calculate the range of Time data (in column K) with a single formula in cell H8. The formulae to calculate the range can be combined into one. Check the formula in B15.
Plant Number Plant Height (mm) Data 1 112 96.01035 2 102 104.105 3 106 117.7388 4 120 99.2052 5 98 114.1852 6 106 98.52444 7 80 103.1756 8 105 87.03628 9 106 95.38911 10 110 137.6266 11 95 106.5698 12 98 94.90177 13 74 128.4879 14 112 84.23827 15 115 76.42927 16 109 111.8783 17 100 91.34343 93.98116 Mean 102.8235 Mean 103 Mean 103.2426 96.77356 Variance 137.7794 Variance 138 Variance 290.9495 78.95172 Standard Deviation 11.7379 SD 11.7 Standard Deviation 17.05724 139.2985 115.4865 Mean, Variance, and Standard Deviation can be calculated by using a single formula (see B20, B21, and B22). For Variance, use the functions "VAR" or "VAR.S" and for Standard Deviation, "STDEV" or "STDEV.S". We are showing many decimal points in our worksheet, but because the original data only measured 3 significant digits, the values should be reported as: Mean = 103 mm Variance = 138 mm 2 SD = 11.7 mm You can format each cell to show only three significant digits, but Excel keeps more digits internally. Click on E20, 21, 22 and check the formula bar to see what the value is in each cell. Using formulae, calculate the Mean (in M20), Variance (in M21), and Standard Deviation (in M22) of the data in L2:L23.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Plant Number Copy a Number Number Series 1 Number Series 2 Data Labels 1 1 1 1 Sample 1 2 1 2 3 Sample 2 3 1 3 5 Sample 3 4 1 4 7 Sample 4 5 1 5 9 Sample 5 6 1 6 11 Sample 6 7 1 7 13 Sample 7 8 1 8 15 Sample 8 9 1 9 17 Sample 9 10 1 10 19 Sample 10 11 1 11 21 Sample 11 12 1 12 23 Sample 12 13 1 13 25 Sample 13 14 1 14 27 Sample 14 15 1 15 29 Sample 15 16 1 16 31 Sample 16 17 1 17 33 Sample 17 You can enter data like this by typing 1, 2, 3, ...etc. in each cell. Or you can let Excel autofill data. Let's see how this works. Filling Cells with the Same Number Select E2 like this: Grab the little square at the bottom right of the cell and drag it down to E18. When you let go, Excel fills in all the cells with the same number. Filling Cells with a Number Series Select K2, drag to K3, and let go. You have now selected both K2 and K3 like this: Grab the little square at the bottom right of the cells and drag it down to K18. When you let go, Excel fills in all the cells with a number series. Filling Cells with a Different Number Series Select Q2, drag to Q3 and let go. You have now selected both Q2 and Q3 like this: Grab the little square at the bottom right of the cells and drag it down to Q18. This time, when you let go, Excel fills the cells with a number series that increments by two each time. Filling Cells with an Ordered Series of Text Excel can work with cell content that contains both text and a number. Perform a similar exercise using Column W. See what happens when you try to autofill a sequence like: Sample 1 Sample 2
Plant Number Plant Height (mm) Plant Height (m) 1 112 0.112 2 102 0.102 3 106 0.106 4 120 0.12 5 98 0.098 6 106 0.106 7 80 0.08 8 105 0.105 9 106 0.106 10 110 0.11 11 95 0.095 12 98 0.098 13 74 0.074 14 112 0.112 15 115 0.115 16 109 0.109 17 100 0.1 Mean 102.8235294 0.102823529 Select C2. Grab the little square at the bottom right of the cell and drag it down to C18. Click in each cell in Column C to see what happened. Excel filled in the cells by automatically incrementing the cell references in the formula. For example, C2 uses the value in B2. One below, C3, uses the value in B3, etc. Because the cell reference changes when you copy the formula to a different location, this is known as Relative Reference. To copy a formula from a cell, select a cell, then: On a PC, select Copy from the Clipboard section of the Home tab Or use the shortcut key for copy (Ctrl-c) On a Mac, select from the menu bar Edit -> Copy Or use the shortcut key (command-C) Then, to paste the formula into a cell, On a PC, select Paste from the Clipboard section of the Home tab Or use the shortcut key for paste (Ctrl-v) On a Mac, select from the menu bar Edit -> Paste Or use the shortcut key (Command-v) Excel can also fill cells with formulae. Let's see how this works using the formula in C2 (=B2/1000) that converts millimeters to meters. The same thing would happen if you copy and paste formulae. Refer to the blue boxes to the right if you don't know how to copy and paste. Click on C18, then copy the content of the cell (should be the formula "=B18/1000"). Then click on C20 and paste the content of the clipboard into cell C20. C20 now has "=B20/1000", not "=B18/1000".
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.90 347 9.30 352 7.70 356 8.50 413 8.20 420 9.70 422 10.30 428 10.20 452 8.90 456 9.60 457 7.85 Mean 9.11 458 9.60 Variance using function 0.774621429 461 9.80 Standard Deviation using function 0.880125803 462 8.80 468 9.00 503 9.10 506 9.20 507 8.80 509 9.20 511 8.80 512 9.40 519 8.30 522 8.40 561 10.20 564 9.30 605 10.20 609 10.50 610 9.00 611 9.80 619 9.30 621 7.60 674 10.50 676 9.70 687 8.60 Columns G and H contain data from 50 medium ground finches. Column L shows the ID # of a bird, and column M shows its beak depth. Using functions, calculate the following values for the beak depth of these birds: Mean in D29; Variance in D30; Standard Deviation in D31 Use the mean value in D29 and absolute referencing method to calculate the (Beak Depth - mean) in column I, and the square of those values in column J. Use the values in column J to calculate the sum of squares in D33. Calculate n - 1 of column J in D34. Calculate the variance in D35 using the calculated values in D33 and D34. And finally, calculate the standard deviation by using the value in D36. Verify that D30 = D35 and D31 = D36.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help