Thanks Thanks:  0
Results 1 to 3 of 3

Thread: Excel Question

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Oct 2017
    Location
    Tasmania
    Posts
    132
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Question

    Worked great! Thanks NiMip!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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