+ Reply to Thread
Results 1 to 6 of 6

Average over the last 30 days

  1. 01-13-2014, 07:24 PM #1
    Timmays
    Timmays is offline
    Registered User
    Join Date
    01-13-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    2

    Average over the last 30 days

    Hey guys,

    I'm sorry, I'm new to this, so I apologize if this has been answered or I'm not following correct protocol. I'm having trouble with calculating an average over the last 30 days, and through everything I've been trying to find via googling, I can't seem to get an answer I understand. Basically, I have an excel sheet with rows of data, and columns with the dates which I populate every day. To throw another wrench into this, I only take weekdays into consideration, so it would have to be the last 30 populated columns. I've got the average in general, but I would like to see that figure compared more recent data, which I can't seem to find. So as an example, here's I simplified version of what I see, along with what I'd like to see:

    Column A B C AD AE AF AG BS BT

    Average Last 30 day Avg 11/1/2013 11/4/2013 11/5/2013 11/6/2013.....12/30/2013 12/31/2013
    Tickets 10.5 ??? 10 12 11 9 12 8


    Any help with this would be greatly appreciated!!!
    Reply With QuoteRegister To Reply
  2. 01-13-2014, 07:44 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Average over the last 30 days

    Hi and welcome to the forum

    It will be easier to offer suggestions if we could see a (clean) sample of what you are working with

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 01-13-2014, 08:12 PM #3
    Timmays
    Timmays is offline
    Registered User
    Join Date
    01-13-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Average over the last 30 days

    Hey, thanks for the response and sorry about that! I made up a sample excel sheet, didn't realize the formatting wouldn't carry forward in the post. Thanks in advance!Sample Excel.xlsx
    Reply With QuoteRegister To Reply
  4. 01-14-2014, 12:54 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Average over the last 30 days

    OK try this...
    =AVERAGE(OFFSET($A2,0,COUNT($D$1:$ZZ$1)-28,1,COUNT($D$1:$ZZ$1)))
    Reply With QuoteRegister To Reply
  5. 01-14-2014, 01:26 AM #5
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Average over the last 30 days

    FDibbins, your result is 9.645 while manual calculation is giving 9.533

    May be this:

    Please Login or Register  to view this content.
    Quang PT
    Reply With QuoteRegister To Reply
  6. 01-14-2014, 01:30 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Average over the last 30 days

    @ bebo, thx for the correction, it should have been...
    =AVERAGE(OFFSET($A2,0,COUNT($D$1:$ZZ$1)-27,1,COUNT($D$1:$ZZ$1)))

    I was calc'ing 31 days, not 30
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Average Score for last 30 days, 60 days, etc.
    By kublaieod in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-02-2013, 05:00 AM
  2. Help on count the number of days in between dates and then average number of days
    By Barbara Excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2013, 12:13 PM
  3. Average of last 10 days
    By DonMashak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-04-2011, 11:56 PM
  4. Average number of days
    By ncmal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2009, 02:35 PM
  5. Average a range IF within 30 days
    By earthtodan in forum Excel General
    Replies: 10
    Last Post: 06-05-2008, 11:50 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1