How does the excel function AVERAGEIF work with dates?

I am showing here two answers that I gave to the above question that came to me from http://www.quora.com. There are other answers there that you might want to explore too.

Based solely on the question, I posted this:

Original answer: I have attached a screenshot of a small example I created for you. You can see the formulas I used in the cumulative average column CumAve and in the cell where I used AVERAGEIF().

Let me know of any problems with this.

 
I then got this feedback from the questioner and added some new information.
–oo0oo–

Hi Duncan, very kind of you. I’ll explain my background, since it differs slightly from your model.
I have historical data of performances of stocks and index of the past 70 years and I’d like to see what is the average performance of one given ticker on a specific day in these past decades (eg how has S&P500 performed on 7th April).
The formula I’d need is something like =AVERAGEIF(range of dates and value, 07/04, range of values of that speficic day).

Yet, when I search across dates, the formula doesn’t work. Perhaps I should work around the format to plain text?

–oo0oo–

Following Marco’s additional information I have created a file of data taken from the S&P 500 data set (download the file below). What I have done is to take the data, convert it into an Excel Table and then use Get & Transform (Power Query) to sort out the dates for me … eg … 1st January 2017 became

  • Day 1
  • Month 1
  • Year 2017

With the day number in its own column, month number in its own column and year number in its own column … see the file to see what I mean.

I did a few more things that I have written in my Excel file. By doing what I have done, you will find that when you update your source table, the Query will update its own table automatically … by Excel Magic.

Open the file and see my notes on the sp500_data (2) tab

Here is the query once I have selected 4th April for all years …

In the end this is what I did. It is possible to make the changes to the dates in other ways but Power Query does them really easily and it has the bonus of being able to update in real time as you update your data.

Download my file from here sp500_data_for_quora_question

Duncan Williamson

18th September 2017