This video tutorial demonstrates the quantitative data analysis process for fish selection within the context of a "Basic Fish Genetics I" practical. The presenter guides viewers through using spreadsheet software (like Excel) to calculate key statistical metrics such as averages and standard deviations for fish weight data across different experimental groups. A core aspect of the tutorial involves simulating subsequent selection generations (G0, G1, G2, G3) using the NORM.INV.RAND function, explaining essential data manipulation techniques like locking cell references and utilizing "paste special" for values. The video further details how to calculate inter-generational growth percentages and concludes with instructions on generating a scatter plot to visually represent the selection outcomes.
Summarizing Quantitative Fish Selection Data Analysis
0:00 Introduction: Ferdiansyah Ciptono (NIM C1401231113), representing Group 7, Parallel 2, introduces the quantitative data analysis for fish selection, focusing on data from groups 7, 8, and 9.
0:31 Initial Data Handling and Averages: The process begins by selecting and pasting 30 fish weight data points (from Group 7's data), then calculating their average using the AVERAGE function. This is followed by calculating the average for the "best 16 fish" within the group.
1:27 Global Averages: The presenter then computes the global average weight by combining and averaging data from groups 7, 8, and 9. The average for the presenter's group (Group 7) is stated as 147.26.
2:05 Data Definitions: "Between family" data refers to the data solely from Group 7, while "family" data is described as a mixture of weights from groups 7, 8, and 9.
2:23 Calculating G0 (Initial Generation): Twenty data points are copied to represent the G0 (initial generation) selection. Its average is calculated using AVERAGE, and its standard deviation is calculated using STDEV.S.
3:17 Calculating G1 (First Selection Generation): The G1 values are generated using the formula =NORM.INV.RAND(RAND(),AVERAGE(G0_range),STDEV.S(G0_range)). Crucially, the cells for G0's average and standard deviation are "locked" (using absolute references like $F$5) to allow the formula to be dragged down for all 20 G1 data points.
4:13 Pasting G1 Data and Subsequent Generations: After calculating, the G1 values are selected and pasted as "special values" (using the "123" paste option in Excel) to ensure only the numerical results, not the formulas, are retained. The averages and standard deviations for these 20 G1 values are then calculated.
5:05 Calculating G2 and G3: The procedure for G2 and G3 follows the exact same steps as G1: using the NORM.INV.RAND function based on the previous generation's statistics, followed by pasting as special values and calculating new averages/standard deviations.
5:14 Summarizing Generation Averages: The final average values for G0, G1, G2, and G3 are compiled and pasted together for a summary.
6:23 Calculating Inter-Generational Growth (GG1, GG2, GG3): Growth percentages are calculated for each generation. For example, GG1 is found with the formula =(G1_average - G0_average) / G0_average * 100. Similar logic applies for GG2 (using G2 and G1) and GG3 (using G3 and G2).
7:30 Preparing Data for Plotting (X and Y Axes): For a chart, the X-axis typically uses the average values of G0 through G3. For the Y-axis, the presenter demonstrates creating a frequency count of the selected fish weights (e.g., how many times a specific weight appears) and assigns simplified Y-values (e.g., 3 and 0) based on observed frequencies for plotting.
9:21 Creating a Scatter Chart: The video concludes by showing how to create a scatter plot in Excel. This involves selecting the prepared X and Y data, navigating to "Insert" -> "Recommended Charts," and choosing a scatter plot. The presenter also briefly demonstrates how to add and edit data series within the chart settings.
11:59 Conclusion: The presenter concludes by thanking the viewer and apologizing for any inexperience or errors, stating they only explained what they understood.