-1

I would like to know what is the formula to know the average cost of the last 12 months for each expenses that I have with the current month and that keeps the last 12 months average as the months go by?

To be more precise and for example: now we are in January 2021, I would like to know the average cost from January 2020 to December 2020 for INTERNET

Here's an image of the data:

enter image description here

Here's an image of the tab I want to enter the formula (in cell C2):

enter image description here

Thanks in advance for your response and let me know if my query is not clear enough. Have a good day/evening!

P.S.Sorry for those who can't read french, the file is in french but the dates are formatted in english and for the cost column, it's pretty much universal . I've modified the average column in english (column C in "Budget" tab) to let you know where I want to put the formula.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Max
  • 3
  • 3
  • Hi and welcome to StackOverflow. Please include what you have tried so far in your question. SO is not a "code-factory" to write your code. See [How do I ask a good question](https://stackoverflow.com/help/how-to-ask) and [Help Center](https://stackoverflow.com/help) for guidance on how to ask a good question. – LeoE Jan 12 '21 at 12:09
  • LeoE:Hi LeoE! Thanks for the welcoming! I don't quite get what you mean by "including what I have tried" if the first solution offered worked? – Max Jan 12 '21 at 13:55

1 Answers1

0

Use AVERAGEIFS with EDATE:

=AVERAGEIFS('Despenses'!E:E,'Despenses'!G:G,A2,'Despenses'!C:C,">="&EDATE(EOMONTH(TODAY(),-1)+1,-12))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • It works :)!! Finally after hours of trying different formulas (from a not very advanced skilled level trying to get better in excel coding :P) lol!! Thanks a lot for taking the time to look and give me an answer, greatly appreciated :)!All the best on your personal projects! – Max Jan 12 '21 at 00:24
  • You sound so happy. To make that official, please mark the answer as *Selected*. Not that Scott needs the extra points, he already has 123,000 of them, but it's a nice gesture. – Variatus Jan 12 '21 at 01:02
  • To Scott or anyone who wants to take the time to see Scott's formula: Could somebody tell me the meaning of the numbers in the end of the formula please after the TODAY, the "-1)+1,-12)" ? Thanks in advance! – Max Jan 12 '21 at 01:30
  • Variatus: Indeed, I am happy to finally move on to another step ;). Thanks for letting me know about the "SELECTED" answer. I don't know how the system works here. – Max Jan 12 '21 at 03:15
  • @Max The EOMONTH chooses the last day of the month. the -1 in it chooses the last day of the month prior to today, then it adds one day to get the first of the month. Then the EDATE returns the date -12 months from that date. – Scott Craner Jan 12 '21 at 15:17
  • @Scott: Thanks for the explanations (y)! It's the "-1" and "+1 that can get kind of confusing in the equation when you are not used to how the formula works. – Max Jan 13 '21 at 00:57