Question

1. Hydropsyche caddisflies are macroinvertebrates that live in streams.  Researchers want to be able to estimate the mass (g) of these caddisflies based on body measurements of the macroinvertebrate larvae, such as head capsule width.  Help out these researchers and analyze the data found in ‘Hydropsyche.xlsx’ to predict hydropsyche mass (‘mass’, g) from measurements of head capsule width (‘width’, mm).  Can we predict mass of hydropsyche from head capsule width?

width	weight
0.2625	0.003
0.28	0.019
0.39375	0.029
0.4025	0.052
0.4025	0.045
0.4025	0.049
0.41125	0.021
0.41125	0.047
0.41125	0.032
0.42	0.054
0.4375	0.031
0.4375	0.065
0.455	0.086
0.455	0.094
0.455	0.089
0.525	0.06
0.5425	0.184
0.56	0.086
0.595	0.133
0.63875	0.182
0.665	0.476
0.665	0.244
0.665	0.39
0.665	0.113
0.665	0.162
0.6825	0.297
0.6825	0.242
0.6825	0.302
0.6825	0.2
0.7	0.342
0.7	0.564
0.7175	0.496
0.735	0.34
0.7525	0.329
0.76125	0.814
0.76125	0.553
0.77	0.304
0.7875	0.412
0.7875	0.549
0.7875	0.422
0.805	0.346
0.805	0.31
0.81375	0.618
0.8225	0.54
0.8225	0.688
0.8225	0.452
0.8225	0.245
0.83125	0.397
0.8575	0.53
0.8575	1.076
0.8575	1.113
0.8575	0.301
0.8575	0.646
0.875	1.179
0.875	0.889
0.875	0.658
0.875	0.737
0.875	0.632
0.875	0.494
0.8925	0.55
0.91	0.936
0.9275	0.375
0.945	1.531
1.015	0.39
1.1025	0.627
1.12	1.383
1.1375	0.993
1.1375	1.192
1.155	1.242
1.155	1.705
1.1725	0.768
1.1725	1.231
1.19	0.618
1.2075	1.794
1.225	3.197
1.2425	4.288
1.2425	4.641
1.2775	1.562
1.2775	1.474
1.295	1.722
1.3125	3.631
1.3125	1.257
1.3125	4.125
1.3125	2.354
1.3125	2.032
1.3125	4.002
1.3125	2.343
1.3125	1.981
1.32125	4.56
1.33	4.244
1.33	4.677
1.33	2.762
1.33	3.334
1.33	3.972
1.3475	4.578
1.3475	2.274
1.3475	2.77
1.3475	2.186
1.35625	3.986
1.365	4.243
1.365	2.727
1.365	3.7
1.365	1.542
1.37375	3.185
1.3825	3.471
1.3825	2.035
1.3825	5.744
1.4	4.804
1.4175	2.38
1.4175	4.666
1.4175	5.193
1.435	2.725
1.435	5.326
1.435	2.915
1.4875	8.09
1.4875	5.488
1.54	8.353
1.54	6.926
1.54	9.296
1.5925	5.031
1.61	9.106
1.6275	5.254
1.645	3.102
1.68	2.721
1.68	6.793
1.75	6.366
1.82795699	8.736
1.84946237	4.144
1.87096774	8.512
1.89247312	19.864

Answer 1

We can predict mass of hydropsyche from head capsule width using regression analysis. Following are the steps that are followed for doing regression analysis:

1. On the Data tab, in the Analysis group, click the Data Analysis button.

2. Select Regression and click OK.

3. In the Regression dialog box, configure the following settings:

• Select the Input Y Range, which is your dependent variable. In our case, it's weight.
• Select the Input X Range, i.e. your independent variable. In this example, it's the width.
• Check the Labels box if there are headers at the top of your X and Y ranges.
• Choose your preferred Output option, a new worksheet in our case.

4. Click OK and observe the regression analysis output created by Excel.

Following the regression analysis output:

Based on coefficient table, we can write regression equation as follows:

Weight = -3.430 + 5.390*Width

So, researchers can predict mass of hydropsyche from head capsule width using above regression equation.