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
Function AvgLast(WkRg As Range, WkStg As String, N As Integer) As Double
Dim WkTable()
Dim I As Long, J As Long
WkTable = WkRg
For I = UBound(WkTable, 1) To 1 Step -1
If (WkTable(I, 1) = WkStg) Then
AvgLast = AvgLast + WkTable(I, 2)
J = J + 1
If (J = N) Then Exit For
End If
Next I
AvgLast = AvgLast / J
End Functionand somewhere put =AvgLast(A1:B14,"A",3)
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
Where you have to adjust
{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
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 ??
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;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.
Don’t have an account yet? Register yourself now and be a part of our community!