how to average first n numbers in ms excel-CodePudding

I have two columns in an Excel spreadsheet that look like:-

Key Values
f   1
f   2
u   3
g   4
g   5
h   6
h   7
j   8
j   9
k   10
k   11
k   12

I want to create apply formula which creates an average of first n numbers in ms excel.

I Try this:-

=AVERAGE(B:B,10)

but could not get the answer.

Please help me for give me appropriate answer.

CodePudding user response：

Avoid volatile set-ups with OFFSET or INDIRECT where possible:

=AVERAGE(B2:INDEX(B:B,n 1))

Or, as pointed out by @JvdV:

=AVERAGE(TAKE(B:B,n 1))

Replace n as desired.

CodePudding user response：

in cell E1: enter the number of rows to take into account for average

in cell F1: enter ="B1:B"&E1

in cell E4: enter =AVERAGE(INDIRECT(F1))

Rgds Frederik