+ 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
    Pro 2019
    Posts
    15,417

    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.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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