Search code examples
excelaverage

Find the Average of Groups of numbers in a range based on their proximity to each other in Excel

Column A has a range of numbers. I would like to compare each number to every other number in Column A. I would like an average for each number of all the values that have a difference of less than 2. The desired output would be in column B.

This is the desired output:

This is the desired output

I've tried many versions of this: =AVERAGEIF(A:A,ABS((A:A)-A9)<2,A:A) and get a #SPILL! error. Any help would be much appreciated

Solution

  • You need to decide whether it is a condition <2 or <=2.
    The expected results point to the latter option.
    Formula that can be used in B1 (copy down as required)

    =AVERAGE(IF(INDEX(ABS($A$1:$A$5-TRANSPOSE($A$1:$A$5)),,ROW(B1))<2,$A$1:$A$5))

    Change to <=2 if necessary.
    Average2