+ Reply to Thread
Results 1 to 2 of 2

Average Calculation that can Determine which Month it Is

  1. #1
    Registered User
    Join Date
    01-17-2019
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Average Calculation that can Determine which Month it Is

    Hey Team,

    I have a question regarding a calculation to find an average. I need a formula that takes the average of a range that only averages what has been recorded to date. I have several sum calculations for upcoming months (row 5 that sums rows 6-9) that I do not want to be considered for this average until that month has passed and I have the sales data for it. Currently these 0's for Jan-Jun are factoring into my average in cell O5 (yellow). I want this formula to know to only calculate the average based on the current date and previous months. In other words, it is Jan-19 now, and I want my formula to calculate an average that includes values from Jul-18 through Dec-18 and not include values from Jan-19 through Jun-19. I also want this formula to be able to recognize when it turns from January to February to now take the average of Jul-18 through Jan-19. This calculation really only applies to row 5 as I can simple average calculation for the other rows. I realize that I could do this same average function and update month to month as well as delete and reenter the sums in row 5 as the months progress, but I would prefer to have a formula that can just do it all for me.

    Average.PNG

    Thanks for your help,
    'Dozer

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,232

    Re: Average Calculation that can Determine which Month it Is

    Welcome to the Forum bill_dozer! My son is at VCU, nice town you have there.

    You want to use AVERAGEIF, if you have Excel 2007 or later (the version in your profile says "32-bit" but that's not what we're looking for when we ask for version).

    =AVERAGEIF($B$2:$M$2,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1),$B3:$M3)

    This will average values for months earlier than the current month. This assumes that the values in row 2 are real dates, not text.

    If this doesn't work please attach your file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

+ 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. month to date calculation based on month selection from list box
    By jamaludheen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2017, 06:00 AM
  2. [SOLVED] Calculation of days per month for dates beginning in one month and ending in another month
    By Fahrettin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2015, 04:52 PM
  3. How to determine whether month follows or precedes a set month?
    By tinytutu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2014, 01:15 AM
  4. [SOLVED] Determine work days in current month or next month based on day of the month
    By sbrnard in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2014, 05:14 PM
  5. Replies: 0
    Last Post: 05-17-2014, 10:18 PM
  6. Three month average calculation in pivot table
    By Aland2929 in forum Excel General
    Replies: 1
    Last Post: 09-26-2012, 04:26 AM
  7. Replies: 4
    Last Post: 08-09-2006, 09:59 AM

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