Easier process

[royzvelt]

I have the below data:
[TABLE="width: 1368"]
<colgroup><col width="72" span="19" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72"][TABLE="width: 1368"]
<colgroup><col width="72" span="19" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 72"][/TD]
[TD="class: xl65, width: 72"]a[/TD]
[TD="class: xl65, width: 72"]b[/TD]
[TD="class: xl65, width: 72"]c[/TD]
[TD="class: xl65, width: 72"]d[/TD]
[TD="class: xl65, width: 72"]e[/TD]
[TD="class: xl65, width: 72"]f[/TD]
[TD="class: xl65, width: 72"]g[/TD]
[TD="class: xl65, width: 72"]h[/TD]
[TD="class: xl65, width: 72"]i[/TD]
[TD="class: xl65, width: 72"]j[/TD]
[TD="class: xl65, width: 72"]k[/TD]
[TD="class: xl65, width: 72"]l[/TD]
[TD="class: xl65, width: 72"]m[/TD]
[TD="class: xl65, width: 72"]n[/TD]
[TD="class: xl65, width: 72"]o[/TD]
[TD="class: xl65, width: 72"]p[/TD]
[TD="class: xl65, width: 72"]q[/TD]
[TD="class: xl65, width: 72"]r[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]0.5[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.0[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.8[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]0.4[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]1.0[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]1.0[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.0[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]0.3[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]0.3[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]0.8[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]0.4[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]1.0[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]1.0[/TD]
[TD="class: xl66"]0.0[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.9[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]0.5[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]1.0[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.0[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]0.7[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.1[/TD]
[TD="class: xl66"]0.4[/TD]
[TD="class: xl66"]0.7[/TD]
[TD="class: xl66"]0.5[/TD]
[TD="class: xl66"]0.3[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.9[/TD]
[TD="class: xl66"]0.2[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to take the take the sum of ((a1+b1(/2)^2 (so that would be cell B2 and C2) for each 1 through 6 and then add that to the sum of (A1-B1)^2 for all 6 as well. I then want to do that for every combination (A and B, A and C, A and D, B and C etc..). The end result would look like this:

[TABLE="width: 1368"]
<colgroup><col width="72" span="19" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 72"][/TD]
[TD="class: xl65, width: 72"]a[/TD]
[TD="class: xl65, width: 72"]b[/TD]
[TD="class: xl65, width: 72"]c[/TD]
[TD="class: xl65, width: 72"]d[/TD]
[TD="class: xl65, width: 72"]e[/TD]
[TD="class: xl65, width: 72"]f[/TD]
[TD="class: xl65, width: 72"]g[/TD]
[TD="class: xl65, width: 72"]h[/TD]
[TD="class: xl65, width: 72"]i[/TD]
[TD="class: xl65, width: 72"]j[/TD]
[TD="class: xl65, width: 72"]k[/TD]
[TD="class: xl65, width: 72"]l[/TD]
[TD="class: xl65, width: 72"]m[/TD]
[TD="class: xl65, width: 72"]n[/TD]
[TD="class: xl65, width: 72"]o[/TD]
[TD="class: xl65, width: 72"]p[/TD]
[TD="class: xl65, width: 72"]q[/TD]
[TD="class: xl65, width: 72"]r[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]2.11[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]2.22[/TD]
[TD="class: xl65"]2.27[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]1.67[/TD]
[TD="class: xl65"]1.92[/TD]
[TD="class: xl65"]2.20[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"]2.23[/TD]
[TD="class: xl65"]2.33[/TD]
[TD="class: xl65"]3.11[/TD]
[TD="class: xl65"]2.16[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]2.16[/TD]
[TD="class: xl65"]2.11[/TD]
[TD="class: xl65"]2.22[/TD]
[TD="class: xl65"]2.08[/TD]
[TD="class: xl65"]2.41[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]g[/TD]
[TD="class: xl65"]2.67[/TD]
[TD="class: xl65"]2.68[/TD]
[TD="class: xl65"]2.92[/TD]
[TD="class: xl65"]2.50[/TD]
[TD="class: xl65"]3.17[/TD]
[TD="class: xl65"]3.20[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]h[/TD]
[TD="class: xl65"]2.62[/TD]
[TD="class: xl65"]3.14[/TD]
[TD="class: xl65"]3.19[/TD]
[TD="class: xl65"]2.77[/TD]
[TD="class: xl65"]3.52[/TD]
[TD="class: xl65"]3.46[/TD]
[TD="class: xl65"]3.37[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]i[/TD]
[TD="class: xl65"]1.67[/TD]
[TD="class: xl65"]1.96[/TD]
[TD="class: xl65"]2.15[/TD]
[TD="class: xl65"]1.80[/TD]
[TD="class: xl65"]2.17[/TD]
[TD="class: xl65"]1.94[/TD]
[TD="class: xl65"]2.67[/TD]
[TD="class: xl65"]2.96[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]j[/TD]
[TD="class: xl65"]2.10[/TD]
[TD="class: xl65"]2.41[/TD]
[TD="class: xl65"]3.11[/TD]
[TD="class: xl65"]2.13[/TD]
[TD="class: xl65"]1.99[/TD]
[TD="class: xl65"]2.15[/TD]
[TD="class: xl65"]3.51[/TD]
[TD="class: xl65"]3.48[/TD]
[TD="class: xl65"]2.15[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]k[/TD]
[TD="class: xl65"]2.17[/TD]
[TD="class: xl65"]2.16[/TD]
[TD="class: xl65"]2.77[/TD]
[TD="class: xl65"]1.93[/TD]
[TD="class: xl65"]2.18[/TD]
[TD="class: xl65"]2.31[/TD]
[TD="class: xl65"]2.86[/TD]
[TD="class: xl65"]3.22[/TD]
[TD="class: xl65"]2.12[/TD]
[TD="class: xl65"]2.19[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]l[/TD]
[TD="class: xl65"]2.71[/TD]
[TD="class: xl65"]2.64[/TD]
[TD="class: xl65"]2.64[/TD]
[TD="class: xl65"]2.41[/TD]
[TD="class: xl65"]3.37[/TD]
[TD="class: xl65"]3.14[/TD]
[TD="class: xl65"]2.89[/TD]
[TD="class: xl65"]3.38[/TD]
[TD="class: xl65"]2.71[/TD]
[TD="class: xl65"]3.69[/TD]
[TD="class: xl65"]2.93[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]m[/TD]
[TD="class: xl65"]1.88[/TD]
[TD="class: xl65"]3.03[/TD]
[TD="class: xl65"]2.98[/TD]
[TD="class: xl65"]2.38[/TD]
[TD="class: xl65"]3.05[/TD]
[TD="class: xl65"]2.55[/TD]
[TD="class: xl65"]3.87[/TD]
[TD="class: xl65"]3.33[/TD]
[TD="class: xl65"]2.33[/TD]
[TD="class: xl65"]2.49[/TD]
[TD="class: xl65"]3.01[/TD]
[TD="class: xl65"]3.91[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]n[/TD]
[TD="class: xl65"]2.01[/TD]
[TD="class: xl65"]2.89[/TD]
[TD="class: xl65"]3.19[/TD]
[TD="class: xl65"]2.34[/TD]
[TD="class: xl65"]2.89[/TD]
[TD="class: xl65"]3.40[/TD]
[TD="class: xl65"]3.11[/TD]
[TD="class: xl65"]3.13[/TD]
[TD="class: xl65"]2.45[/TD]
[TD="class: xl65"]3.00[/TD]
[TD="class: xl65"]2.87[/TD]
[TD="class: xl65"]3.23[/TD]
[TD="class: xl65"]2.86[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]o[/TD]
[TD="class: xl65"]1.76[/TD]
[TD="class: xl65"]3.06[/TD]
[TD="class: xl65"]3.16[/TD]
[TD="class: xl65"]2.20[/TD]
[TD="class: xl65"]2.95[/TD]
[TD="class: xl65"]3.20[/TD]
[TD="class: xl65"]3.49[/TD]
[TD="class: xl65"]2.96[/TD]
[TD="class: xl65"]2.46[/TD]
[TD="class: xl65"]2.59[/TD]
[TD="class: xl65"]2.93[/TD]
[TD="class: xl65"]3.54[/TD]
[TD="class: xl65"]2.02[/TD]
[TD="class: xl65"]2.12[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]p[/TD]
[TD="class: xl65"]1.96[/TD]
[TD="class: xl65"]2.14[/TD]
[TD="class: xl65"]1.78[/TD]
[TD="class: xl65"]1.92[/TD]
[TD="class: xl65"]2.84[/TD]
[TD="class: xl65"]1.88[/TD]
[TD="class: xl65"]2.92[/TD]
[TD="class: xl65"]3.01[/TD]
[TD="class: xl65"]1.95[/TD]
[TD="class: xl65"]2.64[/TD]
[TD="class: xl65"]2.50[/TD]
[TD="class: xl65"]2.66[/TD]
[TD="class: xl65"]2.37[/TD]
[TD="class: xl65"]3.08[/TD]
[TD="class: xl65"]2.78[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]q[/TD]
[TD="class: xl65"]2.05[/TD]
[TD="class: xl65"]2.57[/TD]
[TD="class: xl65"]2.69[/TD]
[TD="class: xl65"]2.23[/TD]
[TD="class: xl65"]2.70[/TD]
[TD="class: xl65"]2.20[/TD]
[TD="class: xl65"]3.42[/TD]
[TD="class: xl65"]2.98[/TD]
[TD="class: xl65"]2.23[/TD]
[TD="class: xl65"]2.26[/TD]
[TD="class: xl65"]2.57[/TD]
[TD="class: xl65"]3.46[/TD]
[TD="class: xl65"]2.12[/TD]
[TD="class: xl65"]3.13[/TD]
[TD="class: xl65"]2.56[/TD]
[TD="class: xl65"]2.19[/TD]
[TD="class: xl65"]x[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
[TR]
[TD="class: xl65"]r[/TD]
[TD="class: xl65"]2.71[/TD]
[TD="class: xl65"]3.16[/TD]
[TD="class: xl65"]3.16[/TD]
[TD="class: xl65"]2.73[/TD]
[TD="class: xl65"]3.71[/TD]
[TD="class: xl65"]3.55[/TD]
[TD="class: xl65"]3.30[/TD]
[TD="class: xl65"]3.07[/TD]
[TD="class: xl65"]3.02[/TD]
[TD="class: xl65"]3.79[/TD]
[TD="class: xl65"]3.28[/TD]
[TD="class: xl65"]3.23[/TD]
[TD="class: xl65"]3.46[/TD]
[TD="class: xl65"]3.21[/TD]
[TD="class: xl65"]3.14[/TD]
[TD="class: xl65"]2.98[/TD]
[TD="class: xl65"]3.18[/TD]
[TD="class: xl65"]x[/TD]
[/TR]
</tbody>[/TABLE]

The formula I used to get this is a mess and I was just wondering if there is some easier way to pull this off. The current formula that I used is:
=SUM(((((INDEX($B$2:$S$7,1,MATCH(G$20,$B$1:$S$1,0))))+((INDEX($B$2:$S$7,1,MATCH($F22,$B$1:$S$1,0)))))/2)^2,((((INDEX($B$2:$S$7,2,MATCH(G$20,$B$1:$S$1,0))))+((INDEX($B$2:$S$7,2,MATCH($F22,$B$1:$S$1,0)))))/2)^2,((((INDEX($B$2:$S$7,3,MATCH(G$20,$B$1:$S$1,0))))+((INDEX($B$2:$S$7,3,MATCH($F22,$B$1:$S$1,0)))))/2)^2,((((INDEX($B$2:$S$7,4,MATCH(G$20,$B$1:$S$1,0))))+((INDEX($B$2:$S$7,4,MATCH($F22,$B$1:$S$1,0)))))/2)^2,((((INDEX($B$2:$S$7,5,MATCH(G$20,$B$1:$S$1,0))))+((INDEX($B$2:$S$7,5,MATCH($F22,$B$1:$S$1,0)))))/2)^2,((((INDEX($B$2:$S$7,6,MATCH(G$20,$B$1:$S$1,0))))+((INDEX($B$2:$S$7,6,MATCH($F22,$B$1:$S$1,0)))))/2)^2,(INDEX($B$2:$S$7,1,MATCH(G$20,$B$1:$S$1,0))-INDEX($B$2:$S$7,1,MATCH($F22,$B$1:$S$1,0)))^2,(INDEX($B$2:$S$7,2,MATCH(G$20,$B$1:$S$1,0))-INDEX($B$2:$S$7,2,MATCH($F22,$B$1:$S$1,0)))^2,(INDEX($B$2:$S$7,3,MATCH(G$20,$B$1:$S$1,0))-INDEX($B$2:$S$7,3,MATCH($F22,$B$1:$S$1,0)))^2,(INDEX($B$2:$S$7,4,MATCH(G$20,$B$1:$S$1,0))-INDEX($B$2:$S$7,4,MATCH($F22,$B$1:$S$1,0)))^2,(INDEX($B$2:$S$7,5,MATCH(G$20,$B$1:$S$1,0))-INDEX($B$2:$S$7,5,MATCH($F22,$B$1:$S$1,0)))^2,(INDEX($B$2:$S$7,6,MATCH(G$20,$B$1:$S$1,0))-INDEX($B$2:$S$7,6,MATCH($F22,$B$1:$S$1,0)))^2)

Like I said... a mess. Anyone know if an easier way?

 

[SpillerBD]

??

((a1+b1(/2)^2

 

[royzvelt]

Sorry, that was supposed to be ((A1+b1)/2)^2

 

[SpillerBD]

So the first calculation in your series would be

=((0.5+0.6)/2)^2+((0.5-0.6)^2)

 

[royzvelt]

That would be the very first part of it, but the first cell of the result does that calculation for the 0.4 and 0.7 row and the 0.3 and 0.3 row and sums up the results from all 6 rows.

 

[jamcall]

You might try something like this:

=SUM(((OFFSET($A$2:$A$7,0,MATCH(B$9,$B$1:$S$1,0))+OFFSET($A$2:$A$7,0,MATCH($A11,$B$1:$S$1,0)))/2)^2+(OFFSET($A$2:$A$7,0,MATCH(B$9,$B$1:$S$1,0))-OFFSET($A$2:$A$7,0,MATCH($A11,$B$1:$S$1,0)))^2)

I tested with the data you provided in A1:S7; and had header in row 9 - so this was pasted into B11 (ctrl-shift-enter), which would correspond to the calculation for A and B...

My results are off a bit from your results table so verify that I captured your calculation steps correctly - I also worked it out step-by-step for A and B and got the same result as my formula.

HTH,
~ Jim

 

[royzvelt]

That is perfect and MUCH better than my nonsense! Thank you!