Excel Average every 3 rows calculation

Question

I have a set of data in a column A in excel sheet.

I need to calculate average in column B for every 3 data placed in Column A.

Initially I have calculated for the average of A1, A2 and A3 and get the average value in Column B1.
Then I am dragging the B1 cell to calculate the average of A4, A5 and A6 in B2 Cell.

But the probelem is, in B2 cell, the average is calculated for A2, A3 and A4 instead of A4, A5 and A6. I have a lengthy of data to calculate the average. So every time, due to the above issue, I am fixing the column manually for average calculation. How to solve this issue. Advance thanks for your help.

Answer 1

Hi [Siva]

Try any of the following formulas in cell B2 and drag/copy it down

=IFERROR(AVERAGE(OFFSET($A$1,3*(ROW(A1)-1)+1,0,3,1)),"")

Or better

=IFERROR(AVERAGE(OFFSET($A$1,3*ROW(A1)-2,0,3,1)),"")

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Excel 365 Pro Plus with Power Pivot and Power Query.

No formulas.

https://www.mediafire.com/file/pdcwglcawgvzu0m/03_29_22a.xlsx/file

https://www.mediafire.com/file/6qi49vq588ixpoz/03_29_22a.pdf/file

Answer 3

Hi,

If you use Excel 365 then try this formula:

=LET(

    rng,$A$1:$A$100,

    rnga,A1:$A$100,

    data,rng/(INT(SEQUENCE(COUNT(rng),,0)/3)+1=COUNT(rng)-(COUNT(rnga)-1)),

IFERROR(AVERAGE(FILTER(data,NOT(ISERROR(data)))),"")

)

This formula avoid using OFFSET function, which is a volatile function.

HTH

Answer 4

Formula (w/o using OFFSET) in cell B2 to be copied down:

=AVERAGE(INDEX(A$2:A$20,(ROW(A1)-1)*3+1):INDEX(A$2:A$20,(ROW(A2)-1)*3))

Regards,

Amit Tandon

http://www.excelanytime.com/

Answer 5

Hi

With the LAMBDA & Co. functions, a dynamic array in B1:

in B1:

=LET( 

    rng, FILTER(A1:A100,A1:A100<>""), 

    rws, ROWS(rng), 

    MAKEARRAY(ROUNDUP(rws/3,0),1, 

        LAMBDA(r,c, 

            LET( 

                s, (r*3)-2, 

                AVERAGE( INDEX(rng,SEQUENCE(MIN(rws-s+1,3),,s)) ) 

            ) 

        ) 

    ) 

)

OR

=LET( 

    rng, FILTER(A1:A100,A1:A100<>""), 

    rws, ROWS(rng), 

    seq, (SEQUENCE(ROUNDUP(rws/3,0))*3)-2, 

    MAP(seq,rws-seq+1, 

        LAMBDA(x,s, 

            AVERAGE( INDEX(rng,SEQUENCE(MIN(s,3),,x)) ) 

        ) 

    ) 

)