 # Average only the last X numbers in a column (basing on criteria)

• Hi all,

thank you for your ospitality in this forum.

This is my first question:

Let's say i have (column A and B as follow):

A 12
B 5
C 6
A 8
D 4
D 4
A 3
C 7
B 1
A 7
A 9
D 3
C 4
B 1

I need to calculate average for text value A in column B (but not the whole average of the column for A (5 entries), only the LAST 3 (or N values) entries for A so -> 9-7-3). So the result should be 19/3=6,3333. Hope i was clear! Thank you for your help!

• Re: Average only the last X numbers in a column (basing on criteria)

Try next UDF while someone found a formula

and somewhere put =AvgLast(A1:B14,"A",3)

Triumph without peril brings no glory: Just try

• Re: Average only the last X numbers in a column (basing on criteria)

Hi,
Helper column for the last five values in D2 =IF(ROWS(D\$3:D3)>5,"",INDEX(B\$2:B\$12,LARGE(IF(\$A\$2:\$A\$12="A",ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1),ROWS(D\$3:D3)))) C+S+E and fill down to D7
then C1= =AVERAGE(D3:D7)

• Re: Average only the last X numbers in a column (basing on criteria)

assuming

if data is in A1 to B14 for last three A's
D1=IF(ROWS(D\$1:D1)>3,"",INDEX(B\$1:B\$14,LARGE(IF(\$A\$1:\$A\$14="A",ROW(\$A\$1:\$A\$14)-ROW(\$A\$1)+1),ROWS(D\$1:D1)))) with C+S+E fill down to D5
then in C1 =AVERAGE(D1:D5)

• Re: Average only the last X numbers in a column (basing on criteria)

Another possibility
=SUMPRODUCT((\$B\$1:\$B\$14)*((((\$A\$1:\$A\$14="A")*ROW(\$A\$1:\$A\$14)))=LARGE(((\$A\$1:\$A\$14="A")*ROW(\$A\$1:\$A\$14)),{1,2,3})))/3
{1,2,3} and /3 for 3 last numbers
{1,2,3,4} and /4 for 4 last numbers
etc...
and ="A" for the string to search in column A

Triumph without peril brings no glory: Just try

• Re: Average only the last X numbers in a column (basing on criteria)

Thank you to all of you, i've found this excellent solution for my case:

=SUM(IF(ISNUMBER(MATCH(ROW(\$D\$4:\$D\$3678);LARGE(IF(\$D\$4:\$D\$3678=C1080;ROW(\$D\$4:\$D\$3678));ROW(INDIRECT("1:"&AN1080)));0));\$O\$4:\$O\$3678))

this was also to be used on another cell (AN1080 in my case) to reduce the number of entries to the minimum available (in case the occurrencies were less than 5):

=MIN(5; COUNTIF(\$D\$4:\$D\$3678; C1080))

This functions are just amazing ...

NOTE: The above function sum up the last 5 cell values (even if there are 50 occurrencies for that particular string), referring to a certain text string (present in cell C1080).

• Re: Average only the last X numbers in a column (basing on criteria)

Does the first formula working for average ??

Triumph without peril brings no glory: Just try

• Re: Average only the last X numbers in a column (basing on criteria)

=AVERAGE(SUBTOTAL(9,OFFSET(B3,LARGE(IF(A3:A16="A",ROW(A3:A16)-ROW(B3)+1),{1,2,3}),0,1)))
or for N values
=AVERAGE(SUBTOTAL(9,OFFSET(B3,LARGE(IF(A3:A16="A",ROW(A3:A16)-ROW(A3)+1),ROW(INDIRECT("A1:A"&C1))),0,1)))
where C1 is the number of largest

• Re: Average only the last X numbers in a column (basing on criteria)

Quote from PCI;685145

Does the first formula working for average ??

No, it only sum up the last five occurrencies, you have to use the results of the other formula "=MIN(5; COUNTIF(\$D\$4:\$D\$3678; C1080))" and divide it for the other to have the average.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!