top 5 average

Hi All,

I have two formulas here that can do the top 5 average

=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))
or
=IFERROR(AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,5)))),AVERAGE(A:A))
The second formula is an Array formula and must be entered with a Ctrl Shift Enter

But the second formula I am having a bit of trouble understanding.

How does Row(Offset) work? I also tried locking A1 So it is ROW(OFFSET($A$1... but this gave me a different answer to my average and I am confused why.

I understand the first one but the second one I am a bit confused on.

Thank you very much.

Hi Ali,

I see, I just pressed F9 on that portion and it turned out to be {1,2,3,4,5}. So basically in column A1:A10 I just listed down 1,2,3,4,5,6,7,8,9,10 and if I press enter for the formula Row(Offset) it returns 10 but if I use ctrl shift enter it returns 8 <-- which is the correct answer.

I think it must be the array part of the formula but how does that affect the outcome?

I just did the evaluation without {} it becomes 1, but with it becomes {1;2;3;4;5}

I tried to remove the row(offset) portion so =ROW(OFFSET(A1,,,5)) and use this as a stand alone formula but when I use the wizard it returns 1 in both instances. So confusing :[

Ohhhhhhhhhhhhh I think I get it *mini light bulb* thank you Ali for all the help.