Formula to Caculate ave value of last 4 sales of one item?

[tonywatsonhelp]

Hi Everyone,
I think this might be a little more complex that it sounds so I'll try explain all my problems and what i need.

I want a formula that can give me the ave of the last 4 sales of one item.

So in Sheets "Ave" Column D i have a list of SKU's so lets say i'm looking at row 4 for now,

So cell "D4" gives me the SKU "XYZ123"

Now in Sheet "Invoice" Column C are all my Sku's for each sale and in Column G the dates,
So I need to find the last 4 sales add then up and give me the average, however some might not have 4 sales in which case add up as many as there are, if theres none say "No Sales"
Please help if you can
Thanks
Tony

 

[Fluff]

What column do you want to average?

 

[tonywatsonhelp]

Good Point, Sales amounts are in column E

 

[Fluff]

Ok, for 365, how about

=LET(Ary,SORT(FILTER(Invoice!C2:G100,Invoice!C2:C100=D4),5,-1),AVERAGE(INDEX(Ary,SEQUENCE(MIN(4,ROWS(Ary))),3)))

 

[tonywatsonhelp]

wow, i'll give it a go, looks great thanks

 

[Fluff]

Ok,

 

[Eric W]

This will work on earlier versions:

=IFERROR(AVERAGEIFS(Invoice!E2:E100,Invoice!C2:C100,D4,Invoice!G2:G100,">="&AGGREGATE(14,6,Invoice!G2:G100/(Invoice!C2:C100=D4),MIN(4,COUNTIF(Invoice!C2:C100,D4)))),"No Sales")