Function inside the average function

[rajababu]

Hello everyone,
So here is my question. I have used the match function ="B"&MATCH(F2,$A$1:$A$1498,0) to call for a row number which in this case was 51 and since I wanted it to be combined with the B column, i used "B" in front of the match function as you can see so my final output in the cell F9 (where the above formula is) was B51. Easy right? Now, I used the same sort of formula to call for another row number and the output was B76 in the cell F10. Good so far? NOW, what I wish to do is use the average function where I want it to do the job of this formula =AVERAGE(B51:B76) ok? BUT as you can see, the 'text' B51 and B76 is actually written in the cells F9 and F10, respectively. So what I am sort of looking for is this =AVERAGE(=F9:=F10) get it? of course, this formula (with =F9 and =F10 in the average function) does not work and that's why I am asking for help here. I hope someone can help me out with this. To make it easier for you, I am attaching the file here. THANK YOU FOR YOUR EFFORT!! :]

average formula.xlsx

[Jonmo1]

If you remove the "B" from the formulas, and just use the Match
=MATCH(F2,$A$1:$A$1498,0)

Then you can do

=AVERAGE(INDEX(B:B,F9):INDEX(B:B,F10))

Or

=AVERAGE(OFFSET(B1,F9-1,0,F10-F9+1,1))

[rajababu]

Oh wow! Apparently my problem is solved but I would love to learn the function you provided. Can you please explain the index and offset functions as if I was a 10 years old kid?

[:) Sixthsense :)]

You will get lot of materials with examples if you do a google search.

[rajababu]

You will get lot of materials with examples if you do a google search.

Yes, I did. But Jonmo1 used index(B:B,F9) which I have no clue about! By the way, I obviously am a noobie in excel business haha. That's why I'm hoping for Jonmo1 to explain what his formula means since he took a look at my file and fixed the particular problem.

[Jonmo1]

Index(range,row#,col#)

The 3rd argument is optional if the Range is a 1 dimensional range (1 column or 1 row)
The row and column #s are not exactly row/column numbers, they are position#s relative to the range specified.

so Index(B:B,10) refers to the 10th position number in B:B, which is B10
But Index (B5:B20,10) refers to the 10th postion number in B5:B20, which is actually B14

INDEX(B:B,F9):INDEX(B:B,F10)
If F9 = 10 and F10 = 20 then you get
INDEX(B:B,10):INDEX(B:B,20)
B10:B20



Offset(range,#ofrows,#ofcolumns,height,width)
It creates a range that is the designated number of rows/columns away from range.
Then resizes that range by the designated height and width.
Height and Width arguments are optional, if omitted the resulting range is the same size as the original referenced range.

OFFSET(B1,F9-1,0,F10-F9+1,1)
If F9 = 10 and F10 = 20
OFFSET(B1,10-1,0,20-10+1,1)
OFFSET(B1,9,0,11,1)

So it creates a range that is 9 rows down from B1 and 0 columns right from B1 = B10
Then resizes that to be a total of 11 rows and 1 column = B10:B20


Hope that helps.

[rajababu]

Index(range,row#,col#)

The 3rd argument is optional if the Range is a 1 dimensional range (1 column or 1 row)
The row and column #s are not exactly row/column numbers, they are position#s relative to the range specified.

so Index(B:B,10) refers to the 10th position number in B:B, which is B10
But Index (B5:B20,10) refers to the 10th postion number in B5:B20, which is actually B14

INDEX(B:B,F9):INDEX(B:B,F10)
If F9 = 10 and F10 = 20 then you get
INDEX(B:B,10):INDEX(B:B,20)
B10:B20



Offset(range,#ofrows,#ofcolumns,height,width)
It creates a range that is the designated number of rows/columns away from range.
Then resizes that range by the designated height and width.
Height and Width arguments are optional, if omitted the resulting range is the same size as the original referenced range.

OFFSET(B1,F9-1,0,F10-F9+1,1)
If F9 = 10 and F10 = 20
OFFSET(B1,10-1,0,20-10+1,1)
OFFSET(B1,9,0,11,1)

So it creates a range that is 9 rows down from B1 and 0 columns right from B1 = B10
Then resizes that to be a total of 11 rows and 1 column = B10:B20


Hope that helps.

THANK YOU so much for the explanation! Problem is solved!