Comparing daily data with monthly average

Hi all,

I have a huge database with daily data from 1971 to 2013, and each day has one entry.

What I am trying to do is find the difference between the days value and that month's average.
In other words:
(1/Jan/1971) - (Average(Jan 1971))
(2/Jan/1971) - (Average(Jan 1971))
:
(20/July2013) - (Average(July 2013))

I have made a pivot table and can find the average for each month and I can also have the values show up un-grouped, but I have not found a way of comparing the two.
Given the size of the file and the fact that the months are not regular, I was hoping you guys might be able to help!

What are the columns for date and data?

I was able to do this with a helper column.

Insert this formula in a blank column:

Formula:

Please Login or Register  to view this content.

where A1 is the date you're referring to. Next, drag the formula down. This will group all months together by year.

In the next column over put:

Formula:

Please Login or Register  to view this content.

And enter it as an array (Control + Shift + Enter)

The formulas above assume column A has dates, column B has numbers to compare, and column I has the helper column.

Here's an example. Let us know if this helps you out.

daffodil-dayminusaverage.xlsx


Off to the side, I put actual day minus month average to check the work.

By the months are not regular, I mean that some months are longer than others(30 vs 31 days), and that the weekends don't show up eg 9 and 10 January or 16 and 17 January. Note also that some days don't have data such as 12 and 15 February. I am not familiar enough with working with dates to know how to deal with this.

I have attached a sample of the data, with the first two months filled in with numbers I worked out manually.

Sample Data Forex.xlsx
Thanks for your help so far.

With an Autofilter I filter all ND values and delete thise item.
Then I inserted a new blank column A

See the solution of that in Sheet2

Hi guys, on second thoughts, I might as well post the whole problem in case you have suggestions on a better way to do it.
Basically, what I am trying to do is see if volatility has increased in foreign exchange markets since currencies began to be floated.

The way I am thinking of doing this is by calculating the standard deviation for each month and then plotting it on a graph.

I have included a sample sheet below.full problem.xlsx