Excel Question

[YahooGoogle]

I have a file that has two columns "Where" and "Region". I also have a "Sales" column: What I would like to know is how to:

a. take the average number of "Sales" that are only from the "East" and only the last 5 numbers, meaning that as I add numbers to my file, it will continue to average only the last 5 sales numbers (so including the new numbers as they are added which in turn would remove the older numbers from the calculations)

b. take the average number of "Sales" to reflect both the "East" and "Away" (so using two columns). Again, as in point a above, I would like only the average of the last 5 sales numbers in the file

[TABLE="width: 249"]
<tbody>[TR]
[/TR]
[TR]
[TD]Here is an example of my file:

[TABLE="width: 313"]
<tbody>[TR]
[TD][/TD]
[TD]Column F
[/TD]
[TD]Column G
[/TD]
[TD]Column H
[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]Sales
[/TD]
[TD]Where
[/TD]
[TD]Region
[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]22[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]17[/TD]
[TD]home[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]21[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]20[/TD]
[TD]home[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]24[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD]24[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD]24[/TD]
[TD]home[/TD]
[TD]west[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD]22[/TD]
[TD]home[/TD]
[TD]west[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD]25[/TD]
[TD]away[/TD]
[TD]west[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD]11[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD]35[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD]9[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 14[/TD]
[TD]13[/TD]
[TD]home[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 15[/TD]
[TD]17[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 16[/TD]
[TD]13[/TD]
[TD]home[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 17[/TD]
[TD]27[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 18[/TD]
[TD]12[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 19[/TD]
[TD]14[/TD]
[TD]home[/TD]
[TD]west[/TD]
[/TR]
[TR]
[TD]Row 20[/TD]
[TD]7[/TD]
[TD]home[/TD]
[TD]west[/TD]
[/TR]
[TR]
[TD]Row 21[/TD]
[TD]20[/TD]
[TD]away[/TD]
[TD]west[/TD]
[/TR]
[TR]
[TD]Row 22[/TD]
[TD]14[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
[TR]
[TD]Row 23[/TD]
[TD]15[/TD]
[TD]away[/TD]
[TD]east[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]For point A, I tried using something like: =AVERAGEIF(H:H,H2,OFFSET(F1,COUNT(F:F),0,-5))

but the end result wasn't accurate. I can't even begin to imagine where to start for point B

Any help would be greatly appreciated!
Thanks[/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[NiMip]

Are you happy to add a couple of columns to your data? I think the solution below works. Note the AVERAGE formulas in bold are array formulas, and you have to hit CONTROL+SHIFT+ENTER for them to work.

[TABLE="class: grid, width: 710"]
<tbody>[TR]
[TD]Sales[/TD]
[TD]Where[/TD]
[TD]Region[/TD]
[TD]Region Count[/TD]
[TD]Region and Where Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]=COUNTIF($C$2:C2,C2)[/TD]
[TD="align: right"]=COUNTIFS($C$2:C2,C2,$B$2:B2,B2)[/TD]
[TD][/TD]
[TD]Avg East Last 5[/TD]
[TD="align: right"]=AVERAGE(IF((C:C="east")*(D:D>(COUNTIF(C:C,"east")-5)),A:A))[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]home[/TD]
[TD]east[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Avg East & Away Last 5[/TD]
[TD="align: right"]=AVERAGE(IF((C:C="east")*(B:B="away")*(E:E>(COUNTIFS(C:C,"east",B:B,"away")-5)),A:A))[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]home[/TD]
[TD]east[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]home[/TD]
[TD]west[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]home[/TD]
[TD]west[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]away[/TD]
[TD]west[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]home[/TD]
[TD]east[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]home[/TD]
[TD]east[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]home[/TD]
[TD]west[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]home[/TD]
[TD]west[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]away[/TD]
[TD]west[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]away[/TD]
[TD]east[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[YahooGoogle]

Worked great! Thanks NiMip!