Search code examples
arraysexcelaveragearray-formulas

Excel average rows to array formula

I want to take the average of rows which would result in a column (array). Example input:

3 4

4 4

4 6

With an array formula I want to create:

3.5

4

5

Solution

  • The average is the sum of numbers divided by the count of that numbers. So first add them (A1:A3+B1:B3)
    3+4 = 7
    4+4 = 8
    4+6 = 10
    Then divide by the number of numbers(/2):
    7/2 = 3.5
    8/2 = 4
    10/2 = 5 

    {=(A1:A3+B1:B3)/2}

    edit after comment from op:
    formula for addition without adding column manually from https://productforums.google.com/forum/#!topic/docs/Q9x44sclzfY

    {=mmult(A1:B3,sign(transpose(column(A1:B3))))/Columns(A1:B3)}