Created by David Hagar
In a lab, a test is performed in triplicate. If 2 of the values are the
same, those 2 values are averaged. Otherwise, all 3 of the values are
averaged. An array formula that returns a result based on this protocol is:
where Rng is a three cell range containing those values.
Editor’s notes:
SUM(COUNTIF(Rng,Rng)) will be 3 when every value is unique. If there are any duplicates, this formula will return more than 3. In the formula on the right, it returns five which can be seen by evaluating the COUNTIF portion of the formula:
The second IF contains the same COUNTIF but compares it to >1. From the partially evaluated formula above, the first and third numbers are the same and therefore return a number greater than 1. The array that’s evaluated in this case is {42.5,"",42.5}. The second value is converted into an empty string so that AVERAGE ignores it.
“If 2 of the values are the same, those 2 values are averaged.”
In practice, this comes to the same as saying:
“If at least 2 of the values are the same, the result of my formula sould be that number.”
Is that right?
The following formula does that and it seems to have the same results as the array formula:
=IF(A1=A2;A1;IF(A1=A3;A1;IF(A2=A3;A2;AVERAGE(Rng))))