+ Reply to Thread
Results 1 to 14 of 14

Help finding last weeks average?

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Help finding last weeks average?

    It help making a formula that looks at a group of cells and determines the average but ignores the last week?

    So i have a bowling team that i run and have a formula that tells me this weeks averages but i also want it to find last weeks average.

    See sample excel sheet.

    Thanks
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-05-2014
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Help finding last weeks average?

    based on your example, there is no way to identify the dates of scores. Do you pull this information from another sheet somewhere?

  3. #3
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: Help finding last weeks average?

    The scores are on the right. I show 10 cells with 6 filled in. This weeks average would be the 6 weeks, last weeks average i want to be 5 weeks of scores.

    Make sense?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help finding last weeks average?

    Hi,

    Not quite sure what the divisor factor 3 is but one way is the array formula (entered with Ctrl-shift-enter)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and for the preceding week
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Richard Buttrey; 12-27-2016 at 12:56 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Help finding last weeks average?

    Another way (also an array formula).

    Last week:
    =INT(AVERAGE(G6:INDEX(6:6,LARGE(IF(G6:P6<>"",COLUMN(G6:P6),""),2)))/3)

    Current week:
    =INT(AVERAGE(G6:INDEX(6:6,LARGE(IF(G6:P6<>"",COLUMN(G6:P6),""),1)))/3)

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: Help finding last weeks average?

    I was using this formula?
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Help finding last weeks average?

    Your formula for the current average is, of course, fine. Providing that the daily value can never be zero. I offered an alternative for both mainly to make it more obvious how it was working - illustrating the difference between finding the last column with something in it and the one immediately before it.

  8. #8
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: Help finding last weeks average?

    I can't really use that formula because of how I setup my worksheet. Please see attached. I have a formula that finds the latest average depending on how many weeks you bowl, but want to find the week before your last time also, to show "last weeks average".

    Thanks
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: Help finding last weeks average?

    Any help is appreciated.

    Thanks

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Help finding last weeks average?

    All you needed to do was adjust the ranges to suit your real sheet. use this in D5:

    =INT(AVERAGE(AM5:INDEX(5:5,LARGE(IF(AM5:AZ5<>0,COLUMN(AM5:AZ5),""),2)))/3)

    array entered, as described above.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Help finding last weeks average?

    All you needed to do was adjust the ranges to suit your real sheet. use this in D5:

    =INT(AVERAGE(AM5:INDEX(5:5,LARGE(IF(AM5:AZ5<>0,COLUMN(AM5:AZ5),""),2)))/3)

    array entered, as described above.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: Help finding last weeks average?

    Thank you very much Glenn. One other quick question, what would I add to the array if they have weeks that are zero? I don't want to erase the zero because it is a equation defined somewhere else.

    Thanks again! A+++++++

  13. #13
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: Help finding last weeks average?

    Thank you very much Glenn. One other quick question, what would I add to the array if they have weeks that are zero? I don't want to erase the zero because it is a equation defined somewhere else.

    Thanks again! A+++++++

  14. #14
    Registered User
    Join Date
    12-04-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2016
    Posts
    55

    Re: Help finding last weeks average?

    Thank you very much Glenn. One other quick question, what would I add to the array if they have weeks that are zero? I don't want to erase the zero because it is a equation defined somewhere else.

    Thanks again! A+++++++

+ 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. 12 weeks rolling average
    By Sri.n in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2014, 05:25 AM
  2. [SOLVED] Average hours across several weeks
    By jaycee66 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2012, 03:24 PM
  3. [SOLVED] Help with finding a specific day/date ten weeks away
    By yeahyeahyeah in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2012, 06:12 PM
  4. Replies: 1
    Last Post: 08-26-2011, 07:43 AM
  5. finding calender weeks for dates
    By venturax in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-05-2007, 04:49 PM
  6. I have 12 scores over 12 weeks . I want to average the highest 5
    By Directioneng in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  7. I have 12 scores over 12 weeks . I want to average the highest 5
    By Directioneng in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  8. I have 12 scores over 12 weeks . I want to average the highest 5
    By Directioneng in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

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