Excel Magic Trick 1406: Calculate Moving Average with AVEARGEIFS & EOMONTH Functions

Download File: people.highline...
See how to use the AVEARGEIFS & EOMONTH Functions with Custom Number Formatting to create a 12 Month Moving Average Report for a two year span.

[@akmalfadhlurrahman7474]

I can't agree more that this is so helpful for my work, great job, keep up the spirit to share all of your holy grail of excel

[@excelisfun]

Yes!!! I am so happy to help : )

[@BiologicalClock]

Our CFO has been using your videos to help create an excel training program for employees at my office, and it's been a huge help. Thank you so much for creating this channel!

[@excelisfun]

You are welcome! Thanks to you and all your fellow co-workers for a Sub and always clicking Thumbs Up : )

[@LeilaGharani]

Great method to show rolling averages. Another way I can think of is to use average with the offset function but the IFS version is easier to write and it probably calculates faster. Thank you!

[@excelisfun]

You are welcome! Thanks for your good ideas with OFFSET and IFS : )

[@chandlerkelly1715]

Thank you! Just found monthly averages for over 80,000 data points! Great solution

[@excelisfun]

Glad it helps!

[@henryg5735]

your videos are really great - no idea how you manage to keep up the quantity and the quality! Many thanks and keep it going!!

[@rodrigocustodio1955]

Thank you Mike, always excellence in teaching, greate delivery with great energy and enthusiasm, you are definetly a very talented teacher, humble, and very smart.

[@asdasvcxvwe5114]

Thank you for the video, I'm surprised no one corrected the wrong formula until now, the EOMONTH should be used at the end and no need to add the -12 and +1 stuff...
"=AVERAGEIFS(DATA_RANGE,DATE_RANGE,">="&START_DATE,DATA_RANGE,"

[@amitkumar-ip6yv]

Mike,that's a cool trick.

[@excelisfun]

Glad you lik eit !!!

[@Taurasltu]

Great video! I have a question. I want to use Running AverageIf formula but can't get it right. Maybe you could help me? Im ok with averagif formula and running average (=AVERAGE($A$1:A1) separately, can't get them together. In my case, I have columns of date customer and amount. I'm trying to find out the average amount of particular customer paid by particular day. Thank You!

[@abdiazizibrahim9163]

I am struggling averageifs. for instance, i need to average unit rates across different projects by consolidating at the parent level(HQ) but the units I am averaging are in different sheets and they all share one budget code? anyone can support me, please

[@pranitpatil5031]

Will you please help me with avg daily balance formula... we are stuck need to figure out how to factor in days with no transaction and need to consider previous days closing balance for all such days... Thanks in advance...👍🏻

[@kevinsantos7276]

What if there is zero?

[@kelvin55141]

Hi do you a formula for rolling 5 days with a criteria eg department?

[@susantorres1827]

Could you PLEASE help me with "A Skills Approach: Excel 2016 Challenge Yourself 10.3" ?

[@sekiondowarema1786]

Thanks,
And if you have zero, it is work!? 😢

[@Victor-ol1lo]

Great video Mike !! Thanks a lot. A DAX solution for moving average would be interesting too :-)

[@brianxyz]

Alberto Ferrari wrote a detailed article on this topic in 2014.
"Rolling 12 Months Average in DAX"
Source: www.sqlbi.com/articles/rolling-12-months-average-in-dax/

[@excelisfun]

If I can get to making one I will. The formula that brianxyz posted about is good, there is at least one other good one too...

[@hosseinhosseinpoor9561]

عالی

[@henryg5735]

Am I missing something? In this and the previous video you use the EOMonth function, add a day, then use >= for the start test. Why not use EOMonth without adding a day, then > on its own?

[@excelisfun]

You are 100% correct. Why did I do that!?!? I guess it related to your other comment on this video... Maybe because I am working all the hours of everyday and am missing some small stuff... Thank you very much for the post and helpful tip : )

[@excelisfun]

Thank you for your tip. I added your example to the downloadable workbook and added an annotation to the video. Thanks : )

[@zaighamuddinfarooqui1705]

Amazing as usual !!!:)

[@excelisfun]

Thank you as usual : )

[@automationguide3498]

Thank You !!

[@excelisfun]

You are welcome! Thanks for your support : )

[@forzaalgiers2045]

Perfect !!

[@excelisfun]

Glad you like it!

[@johnmatta9577]

Thanks Mike, I've done it using Edate formula.
Can that be done using Power Query?

[@excelisfun]

Does the EDATE formula really work with different end of the months?
I am sure that it can be with PQ, but I have not done it yet.

[@johnmatta9577]

Yes it did work..EDATE=(F4,-12)

[@excelisfun]

How did you get EDATE to include the sale on Feb 29, 2016?

[@johnmatta9577]

It's already included in the first average figure which gies from 1 Jan 2016 to 31 Dec 2016...unless I'm missing something which I frequently am

[@excelisfun]

Me too. I often miss things. In fact in the above comment, I wrote: "How did you get EDATE to include the sale on Feb 29, 2016?", when in fact I meant to type: How did you get EDATE to EXCLUDE the sale on Feb 29, 2016?
When I try to use EDATE(F4, -12), for Feb 28, 2017 calculation, since EDATE jumps back to the same day 12 months back, it gets Feb 28, and then the formula incorrect includes the Feb 29 sale.
When I use:
=AVERAGEIFS(fSales6[Sales],fSales6[Order Date],">="&EOMONTH(F4,-12)+1,fSales6[Order Date],""&EOMONTH(F4,-12),fSales6[Order Date],""&EDATE(F4,-12),fSales6[Order Date],"

[@Mohamedali-go6si]

it is amazing >>> thank you very much

[@excelisfun]

You are welcome very much!