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
Bookmarks