YahooGoogle

New Member
Joined
Nov 7, 2017
Messages
14
Office Version
  1. 2007
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

Here is an example of my file:

Column F
Column G
Column H
Row 1Sales
Where
Region
Row 222awayeast
Row 317homeeast
Row 421awayeast
Row 520homeeast
Row 624awayeast
Row 724awayeast
Row 824homewest
Row 922homewest
Row 1025awaywest
Row 1111awayeast
Row 1235awayeast
Row 139awayeast
Row 1413homeeast
Row 1517awayeast
Row 1613homeeast
Row 1727awayeast
Row 1812awayeast
Row 1914homewest
Row 207homewest
Row 2120awaywest
Row 2214awayeast
Row 2315awayeast

<tbody>
</tbody>
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

<tbody>
</tbody>
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.

SalesWhereRegionRegion CountRegion and Where Count
22awayeast=COUNTIF($C$2:C2,C2)=COUNTIFS($C$2:C2,C2,$B$2:B2,B2)Avg East Last 5=AVERAGE(IF((C:C="east")*(D:D>(COUNTIF(C:C,"east")-5)),A:A))
17homeeast21Avg East & Away Last 5=AVERAGE(IF((C:C="east")*(B:B="away")*(E:E>(COUNTIFS(C:C,"east",B:B,"away")-5)),A:A))
21awayeast32
20homeeast42
24awayeast53
24awayeast64
24homewest11
22homewest22
25awaywest31
11awayeast75
35awayeast86
9awayeast97
13homeeast103
17awayeast118
13homeeast124
27awayeast139
12awayeast1410
14homewest43
7homewest54
20awaywest62
14awayeast1511
15awayeast1612

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top