Lab Tests

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:

=AVERAGE(IF(SUM(COUNTIF(Rng,Rng))=3,Rng,IF(COUNTIF(Rng,Rng)>1,Rng,””)))

where Rng is a three cell range containing those values.

Editor’s notes:

range showing two three value columns, the left most having two values the same

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:

SUM({2;1;2})=3

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.

Posted in Uncategorized

One thought on “Lab Tests

  1. “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))))


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.