Re: Average only the last X numbers in a column (basing on criteria)
Quote from PCI;685145Does 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.
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).
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!
