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))))