Average 3 Rows, Display in Column

[i43]

I want to average every 3 rows (row 1, 2, 3 & 5, 6, 7) & display in a separate column (E4 or E8); for example

A B C D E
1 5 5 5 5
2 5 5 5 5
3 5 5 5 5
4 20
5 5 5 5 5
6 5 5 5 5
7 5 5 5 5
8 20

It would continue all the way down about 400 rows. So basically (20+20+20)/3 = 20; How would I do that? I'm not an expert in excel so any help would be greatly appreciated. Also what if sometimes I have 4 rows instead of 3 then how will it change the formula.

[NBVC]

Assuming your data starts at A2:D2, then in E2 enter formula:

Please Login or Register  to view this content.

copied down.

change the E2 to the cell you put your first formula in at same row as your first set of data...

[i43]

I think I didn't explain it properly. I have attached the excel sheet; it's in sheet3. The average column will display the average of the 3 rows above. After the adjustment I have to pull that value into sheet one in the cat column. Please let me know. Thanks

[DonkeyOte]

I think you explained ok given NBVC's solution, however, sample files always help.

Looking at your Sheet3 you can adapt NBVC's solution to your own references:

Please Login or Register  to view this content.

However, given you have the Aggregate for each row already created you can simplify:

Please Login or Register  to view this content.

Regards Col I - I would in turn modify that to:

Please Login or Register  to view this content.

Then regards Sheet1

Please Login or Register  to view this content.

note: Sheet1 formula assumes candidates are listed per Sheet3 - if not you will need to revise to an INDEX/MATCH type approach (or VLOOKUP) with appropriate row offset.

[i43]

THANK YOU SO MUCH!!! It's doing exactly what I need it to do! Thanks again! I'll definitely be back when I need more help!

[i43]

Now I am trying to sort them descending based on the total value, but it mixes up the numbers. So now I would have to do the vlookup or index/match approach? How would I do that? Thank you for helping!

[DonkeyOte]

I would suggest posting a further sample file to illustrate "before" and "after".

[i43]

Okay, I have the totals in column H which I want to sort descending so that I have candidate 1, 5, 2, 4, 3 based on the total column (values 95, 74, 62, 59, 48). Which it does, however when it rearranges the rows it doesn't rearrange columns F & G. It keeps the values 30, 29, 20.5, 21, 16 static while all the other rows change which changes the Totals to 95, 87, 54, 59, 43. How do I have columns F & G stay with the appropriate candidates.

Thank you so much; I really do appreciate your help!

[NBVC]

Try changing the formula in F7 to:

=IF($A7="","",INDEX(Sheet3!$I$4:$I$100,MATCH($A7,Sheet3!$A$4:$A$100,0)+3))

and in G8 to

=IF($A7="","",INDEX(Sheet3!$L$4:$L$100,MATCH($A7,Sheet3!$A$4:$A$100,0)+3))