Rolling MTD Average

%3CLINGO-SUB%20id%3D%22lingo-sub-1963705%22%20slang%3D%22en-US%22%3ERolling%20MTD%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1963705%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20I%20have%20been%20searching%20throughout%20the%20day%20for%20a%20way%20to%20create%20a%20FORMULA%20that%20will%20return%20the%20MTD%20average%20on%20each%20day.%20EX%3A%20Jan%201%20%3D%2010%26nbsp%3B%20%26nbsp%3BJan%202%20%3D%2020%20and%20Jan%203%20%3D%2030.....%20This%20would%20make%20the%20MTD%20AVG%20on%20Jan%201%20%3D10%26nbsp%3B%20%26nbsp%3B%20on%20Jan%202%20%3D%2015%26nbsp%3B%20%26nbsp%3Band%20Jan%203%20%3D%2020.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20seems%20that%20this%20query%20is%20all%20over%20the%20internet%20and%20does%26nbsp%3B%20not%20have%20an%20optimal%20solution.%20I%20have%20been%20abvle%20to%20retrieve%20the%20EOM%20Averages%20on%20a%20daily%20basis%2C%20using%20the%20following%20formula.%26nbsp%3B%3CBR%20%2F%3E%3DAVERAGEIFS(%24B%242%3A%24B%24850%2C%24A%242%3A%24A%24850%2C%22%26gt%3B%3D%22%26amp%3BF2%2C%24A%242%3A%24A%24850%2C%22%26lt%3B%3D%22%26amp%3B%24G2)%3C%2FP%3E%3CP%3EWhere%20A%3D%20Dates%20(Ascending%20order%20from%202017%20-%20today)%26nbsp%3B%20B%3D%20Prices%20%2C%20F%20%3D%20Start%20of%20Month%20Date%20(i.E%20Jan%201%202019)%20and%20G%20%3D%20End%20of%20Month%20Date%20(I.E.%20Jan%2031%202019)%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAttached%20you%20can%20find%20both%20a%20photo%20and%20the%20original%20file.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EUnfortunately%20I%20like%20the%20majority%20of%20users%20I%20found%20online%20need%20this%20in%20Formula%20format%2C%20not%20the%20Pivot%20Table%20or%20Power%20Tools%20solutions.%20Thanks%20in%20advance%20for%20your%20help%2C%20and%20the%20possibility%20to%20consider%20this%20as%20an%20update%20for%20Excel%20versions%20going%20forward.%20Something%20like%20an%20%22%3DWTD%2FMTD%2FYTD_AVG%22%20formula%20haaha.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EEDIT%3A%20Having%20Some%20trouble%20with%20the%20upload%20of%20my%20visuals%20from%20the%20work%20PC.%20Will%20be%20home%20in%20an%20hour%20and%20add%20a%20Photo%20and%20Example%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1963705%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1964059%22%20slang%3D%22en-US%22%3ERe%3A%20Rolling%20MTD%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1964059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893712%22%20target%3D%22_blank%22%3E%40Miner_Of_Metals%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20special%20function%20needed.%20Just%20use%20an%20absolute%20reference%20at%20one%20end%20and%20a%20relative%20reference%20at%20the%20other.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20like%20%3DAVERAGE(%24A1%3AB1)%20and%20copy%20that%20across%20the%20columns.%20Assuming%2C%20obviously%2C%20that%20your%20days%20are%20arranged%20across%20the%20various%20columns%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

@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