Forum Discussion

Miner_Of_Metals's avatar
Miner_Of_Metals
Copper Contributor
Dec 07, 2020

Rolling MTD Average

Hello I have been searching throughout the day for a way to create a FORMULA that will return the MTD average on each day. EX: Jan 1 = 10   Jan 2 = 20 and Jan 3 = 30..... This would make the MTD AVG on Jan 1 =10    on Jan 2 = 15   and Jan 3 = 20. 

It seems that this query is all over the internet and does  not have an optimal solution. I have been abvle to retrieve the EOM Averages on a daily basis, using the following formula. 
=AVERAGEIFS($B$2:$B$850,$A$2:$A$850,">="&F2,$A$2:$A$850,"<="&$G2)

Where A= Dates (Ascending order from 2017 - today)  B= Prices , F = Start of Month Date (i.E Jan 1 2019) and G = End of Month Date (I.E. Jan 31 2019) 

Attached you can find both a photo and the original file. 

Unfortunately I like the majority of users I found online need this in Formula format, not the Pivot Table or Power Tools solutions. Thanks in advance for your help, and the possibility to consider this as an update for Excel versions going forward. Something like an "=WTD/MTD/YTD_AVG" formula haaha. 

EDIT: Having Some trouble with the upload of my visuals from the work PC. Will be home in an hour and add a Photo and Example Excel.

1 Reply

  • mathetes's avatar
    mathetes
    Silver Contributor

    Miner_Of_Metals 

     

    No special function needed. Just use an absolute reference at one end and a relative reference at the other.

     

    Something like =AVERAGE($A1:B1) and copy that across the columns. Assuming, obviously, that your days are arranged across the various columns

Resources