# Average Calculation that can Determine which Month it Is

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

'Dozer

2. ## 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.

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