Average Function

[frdranger25401]

Im scratching my head on a function I have been stumped with for a few days now. I attached a a pic for reference. What im trying to do is type in the search box "H3" that will reference the last 10 entries in column "A" and then it averages the numbers to the right. IE. if I place A1 in the search box at H3, it will reference column A. In my picture example, it will then take B16, B17, B18 and average that number and return the results to another cell. All I have been able to figure out is how to get it to average the last 10 that say A1, which is not what i want it to do. I cant figure how to get it to look at just the last 10 cells regardless of what the text is and just average those 10 cells that have the text A1. Any help would be appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(f,TAKE(FILTER(A2:B100,A2:A100<>""),-10),AVERAGE(FILTER(TAKE(f,,-1),TAKE(f,,1)=H3)))

 

[frdranger25401]

Hi & welcome to MrExcel.
How about

=LET(f,TAKE(FILTER(A2:B100,A2:A100<>""),-10),AVERAGE(FILTER(TAKE(f,,-1),TAKE(f,,1)=H3)))

Your a life saver, That did the job. Thank you for the prompt response

 

[Fluff]

You're welcome & thanks for the feedback.

 

[frdranger25401]

You're welcome & thanks for the feedback.

One additional question. If I wanted it to leave 0's blank and have the function skip over it what would be the best way?

 

[Fluff]

Do you mean to ignore any rows in col B that are blank & not include them as the last 10?

 

[frdranger25401]

Do you mean to ignore any rows in col B that are blank & not include them as the last 10?

Yes, as the function is coming back with an error that i believe is from blank cells

 

[Dave Patton]

I did not see posts 6 and 7 before posting the following.
With a current version of 365, try the following:

T202502a.xlsm
	A	B	C	D	E
1	A	2
2	A	4			91
3	A	6
4	A	8
5	C	10		A	82
6	C	12		B	85
7	A	14		C	94
8	A	16
9	A	18		C	94
10	A	20
2b

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(TAKE(B:.B,H2))
D5:E7	D5	=LET(a,TAKE(A:.B,-10),GROUPBY(INDEX(a,,1),INDEX(a,,2),AVERAGE,,0))
D9:E9	D9	=TAKE(LET(a,TAKE(A:.B,-10),GROUPBY(INDEX(a,,1),INDEX(a,,2),AVERAGE,,0)),-1)
B2:B10	B2	=B1+2
Dynamic array formulas.

 

[Fluff]

the function is coming back with an error

What error?