# Averaging of Scientific Results in Excel

Each scientific measurement process has its own criteria for what is acceptable data. However, there are some general rules. If a data point is suspect, another is generated. If these are within the statistical variation for that process, then they are averaged. However, if a 3rd measurement is needed,

An Excel formula that correctly handles all of these requirements is needed. If 2 of the measurements are within statistical variation, they are averaged. This would be the two closest values.

But if two measurements are equidistant from the 3rd, (13.2,13.5,13.8) then all 3 values

must be averaged.

So, given those requirements, the following formula will return the correct result.

=IF(SUM(MATCH(\$A\$1:\$C\$1,\$A\$1:\$C\$1,0))<6,QUARTILE(\$A\$1:\$C\$1,1+2*(MEDIAN(\$A\$1:\$C\$1)>SUM(\$A\$1:\$C\$1)/3)),AVERAGE(IF(LARGE(ROUND(ABS(\$A\$1:\$C\$1-TRANSPOSE(\$A\$1:\$C\$1)),3),5)=ROUND(ABS(\$A\$1:\$C\$1-TRANSPOSE(\$A\$1:\$C\$1)),3),\$A\$1:\$C\$1,””)))

First, if the 3 values are all different, then

=SUM(MATCH(\$A\$1:\$C\$1,\$A\$1:\$C\$1,0))=6 is True.

So, if SUM(MATCH(\$A\$1:\$C\$1,\$A\$1:\$C\$1,0))<6,then the formula uses

=QUARTILE(\$A\$1:\$C\$1,1+2*(MEDIAN(\$A\$1:\$C\$1)>SUM(\$A\$1:\$C\$1)/3))

In the comments at this site:

http://chandoo.org/wp/2011/01/19/average-of-closest-2-numbers/

a poster in the comments named Ihm came up with this formula.

In the FALSE condition of the formula is the following:

=AVERAGE(IF(LARGE(ROUND(ABS(\$A\$1:\$C\$1-TRANSPOSE(\$A\$1:\$C\$1)),3),5)=ROUND(ABS(\$A\$1:\$C\$1-TRANSPOSE(\$A\$1:\$C\$1)),3),\$A\$1:\$C\$1,””))

It is based on a 3×3 matrix obtained from

=ROUND(ABS(\$A\$1:\$C\$1-TRANSPOSE(\$A\$1:\$C\$1)),3)

For a data set of

 13.4 13.55 13.8

The following matrix is

{0,0.25,0.4;0.25,0,0.15;0.4,0.15,0}

The ROUND function is necessary since the ABS function sometimes introduces differences (i.e. – 0.25 vs 0.2499999999).

The 5th largest value in the matrix corresponds to the smallest difference. The values for those differences in A1:C1 are averaged to afford the desired result.