Excel Question

Thanks:  0

1. ## Excel Question

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 1 Sales Where Region Row 2 22 away east Row 3 17 home east Row 4 21 away east Row 5 20 home east Row 6 24 away east Row 7 24 away east Row 8 24 home west Row 9 22 home west Row 10 25 away west Row 11 11 away east Row 12 35 away east Row 13 9 away east Row 14 13 home east Row 15 17 away east Row 16 13 home east Row 17 27 away east Row 18 12 away east Row 19 14 home west Row 20 7 home west Row 21 20 away west Row 22 14 away east Row 23 15 away east
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

2. ## Re: Excel Question

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.

 Sales Where Region Region Count Region and Where Count 22 away east =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)) 17 home east 2 1 Avg East & Away Last 5 =AVERAGE(IF((C:C="east")*(B:B="away")*(E:E>(COUNTIFS(C:C,"east",B:B,"away")-5)),A:A)) 21 away east 3 2 20 home east 4 2 24 away east 5 3 24 away east 6 4 24 home west 1 1 22 home west 2 2 25 away west 3 1 11 away east 7 5 35 away east 8 6 9 away east 9 7 13 home east 10 3 17 away east 11 8 13 home east 12 4 27 away east 13 9 12 away east 14 10 14 home west 4 3 7 home west 5 4 20 away west 6 2 14 away east 15 11 15 away east 16 12

3. ## Re: Excel Question

Worked great! Thanks NiMip!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•