+ Reply to Thread
Results 1 to 16 of 16

Rolling/Moving Average: Last "n" values not including blanks in a row

  1. #1
    Registered User
    Join Date
    09-24-2013
    Location
    Shrewsbury
    MS-Off Ver
    Excel 2003
    Posts
    6

    Rolling/Moving Average: Last "n" values not including blanks in a row

    Hi All

    I'm new to the forums so apologies if this has been covered elsewhere.

    I'm trying to create a rolling average of Tenpin bowlers weekly scores with a formula that will find non-blank cells within a row and calculate the average of the last 3 non-blank entries. If there is less than 3 entries it needs to give the average of the entries it finds.

    Example:
    B2 C2 D2 E2 F2 G2 H2
    Player A 161 210 144
    Player B 145 143 144

    Player A Rolling ave. = 171.67 ((161+210+144)/3)
    Player B Rolling ave. = 144.00 ((145+143+144)/3)

    with them adjusting for the next entry:

    B2 C2 D2 E2 F2 G2 H2
    Player A 161 210 144 155
    Player B 145 143 144 163 159

    Player A Rolling ave. = 169.6667 ((210+144+155)/3)
    Player B Rolling ave. = 155.3333 ((144+163+159)/3)

    If it could round the answers down that would be useful too.

    Thanks in advance for your help

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Hi,

    Your data tables seem to have become a little misaligned on posting, so it's a little difficult to tell in which cells your data is intended to be. However, assuming that Player A's scores are in C2:H2 (with perhaps name in B2), enter this array formula (very important that you know how to do this in Excel) in A2 and copy down as required:

    =ROUNDDOWN(AVERAGE(SUBTOTAL(9,OFFSET(C2,,LARGE(IF(C2:H2<>"",COLUMN(C2:H2)-MIN(COLUMN(C2:H2))),ROW($1:$3)),,))),0)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Try this array formula**:

    =IF(COUNT(B2:H2),INT(AVERAGE(IF(B2:H2>0,IF(COLUMN(B2:H2)>=LARGE(IF(B2:H2>0,COLUMN(B2:H2)),MIN(3,COUNT(B2:H2))),B2:H2)))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-24-2013
    Location
    Shrewsbury
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Thanks both, I'm sure both formulas will give me the answer I'm looking for however I perhaps should have put the exact cells I require in the example rather than trying to simplify things for explanation purposes!

    I've modified both answers for my table and they fail.

    Have attached my file (hopefully...) so would be grateful if you could re-work the formulas to suit. Rolling average needs to be calculated in column W.

    Shrews Singles Bowler Tracking.xls

    Cheers once again

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Sorry, but your file is too big for me. I have a 50kb download limit.

  6. #6
    Registered User
    Join Date
    09-24-2013
    Location
    Shrewsbury
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Quote Originally Posted by Tony Valko View Post
    Sorry, but your file is too big for me. I have a 50kb download limit.
    Ah ok Tony - basically player name(s) are in column A, scores entered/too be entered are in Columns B to V and rolling average needs to be calculated in Column W.

    Due to header rows, data entry starts at row 3.

    I amended your formula and entered it as an array formula to:
    {=IF(COUNT(B3:V3),INT(AVERAGE(IF(B3:V3>0,IF(COLUMN(B3:V3)>=LARGE(IF(B3:V3>0,COLUMN(B3:V3)),MIN(3,COUNT(B3:V3))),B3:V3)))),"")}

    But it failed

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Well,

    If you amend my formula (in W3) to:

    =AVERAGE(SUBTOTAL(9,OFFSET(C3,,LARGE(IF(C3:V3<>"",COLUMN(C3:V3)-MIN(COLUMN(C3:V3))),ROW(INDIRECT("1:"&MIN(3,COUNT(C3:V3))))),,)))

    However, I've had to remove the ROUNDDOWN to make the level of nesting compatible for 2003. Perhaps Tony's solution will avoid this issue.

    P.S. Still not sure if column B (Entering Ave.) should be included in the average?

    Regards

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Works OK for me.

    When you say it fails, what does that mean? Do you get an error? An incorrect result? Something else?

    Are the empty cells truly empty?

    What version of Excel does this have to work in?

  9. #9
    Registered User
    Join Date
    09-24-2013
    Location
    Shrewsbury
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Quote Originally Posted by XOR LX View Post

    P.S. Still not sure if column B (Entering Ave.) should be included in the average?

    Regards
    Only for those players with less than 3 entries, after that it rolls to the last 3 anyway so should not be included in the calculation. Have amended that to start from B3 and it works a treat thanks ....apart from the rounddown function as you mentioned

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    I'm sure I can get it down to the required level of nesting if I try! Or, like I said, perhaps Tony's solution will not suffer from this drawback. I'll have a look again.

    Regards

  11. #11
    Registered User
    Join Date
    09-24-2013
    Location
    Shrewsbury
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Quote Originally Posted by Tony Valko View Post
    Works OK for me.

    When you say it fails, what does that mean? Do you get an error? An incorrect result? Something else?

    Are the empty cells truly empty?

    What version of Excel does this have to work in?
    Its for 2003, unfortunately my work are tight and wont pay for an upgrade!!

    The cells aren't "truly" empty as they are looking up player name in column A within individual player tables on separate sheet(s). If Player hasn't played for that week entry then it returns a blank cell =IF(S.Broadley!$I$10=0,"",S.Broadley!$I$10)

    The formula {=IF(COUNT(B3:V3),INT(AVERAGE(IF(B3:V3>0,IF(COLUMN(B3:V3)>=LARGE(IF(B3:V3>0,COLUMN(B3:V3)),MIN(3,COUNT(B3:V3))),B3:V3)))),"")} returns a divide by zero error (#DIV/0!)

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Ok, that's the problem (cells not truly empty).

    Try this version (still array entered):

    =IF(COUNT(B3:V3),INT(AVERAGE(IF(ISNUMBER(B3:V3),IF(COLUMN(B3:V3)>=LARGE(IF(ISNUMBER(B3:V3),COLUMN(B3:V3)),MIN(3,COUNT(B3:V3))),B3:V3)))),"")

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Quote Originally Posted by Gooner2408 View Post
    Its for 2003, unfortunately my work are tight and wont pay for an upgrade!!
    I still use Excel 2002 as my default version even though I also have 2007, 2010 and 2013 on my machine.

  14. #14
    Registered User
    Join Date
    09-24-2013
    Location
    Shrewsbury
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Quote Originally Posted by Tony Valko View Post
    Ok, that's the problem (cells not truly empty).

    Try this version (still array entered):

    =IF(COUNT(B3:V3),INT(AVERAGE(IF(ISNUMBER(B3:V3),IF(COLUMN(B3:V3)>=LARGE(IF(ISNUMBER(B3:V3),COLUMN(B3:V3)),MIN(3,COUNT(B3:V3))),B3:V3)))),"")
    Your a WINNER! That's solved all angles and rounds down - BONUS!

    I'd love to sit here and say I understand exactly what the formula does and that I could reproduce it if needed in the future but I cant! I;ve just copied and pasted :D

    Thanks very much for your help and also to XOR LX.

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Good deal. Thanks for the feedback!

  16. #16
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Rolling/Moving Average: Last "n" values not including blanks in a row

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED",as per our Forum Rule #9. I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Return Values in a range not equal to "" criteria. (No Blanks.)
    By acepaul in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-20-2013, 03:30 PM
  2. copying a "moving average" formula?
    By Warstar in forum Excel General
    Replies: 2
    Last Post: 11-22-2010, 02:21 AM
  3. Need help with "Rolling" Average
    By Riley_5000 in forum Excel General
    Replies: 2
    Last Post: 03-02-2009, 06:48 AM
  4. [SOLVED] Adding 2 Values When 3 Conditions are Met, Including an "OR"
    By annem in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-02-2006, 09:45 AM
  5. moving the formula "average" over one column in a macro
    By drumstu in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 09-06-2005, 09:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1