Excel - Currency Conversion based on Average FX Rate in a Pivot Table Grouped by Date

Question

Given 678,792 rows of FX data in excel from a 2 year period which shows the EUR-GBP exchange rate:

DDMMYYYY    HH:MM   FX
28/10/2016  12:15   0.89981
28/10/2016  12:16   0.89989
28/10/2016  12:17   0.89978
28/10/2016  12:18   0.9001
28/10/2016  12:19   0.90017
28/10/2016  12:20   0.90013
28/10/2016  12:21   0.90014

Download source data

How can I pivot the data to:

• Display the Avg FX rate per month
• Calculate the Avg of £500 in EUR for each month

The resulting pivot data should look like this:

Row Labels  Average of FX   Average of £500 in EUR  
- 2015          
 - Qtr1         
    Jan     0.767239686     651.6868312
    Feb     0.741031818     674.7348597
    Mar     0.723227086     691.3457881

I have attempted this by adding a Calculated Field:

[£500 in EUR] = 500/FX

However, this results in the incorrect values. My Pivot table looks like this:

Row Labels  Average of FX   Average of £500 in EUR  
- 2015          
 - Qtr1         
    Jan     0.767239686     0.004339516
    Feb     0.741031818     0.004737974
    Mar     0.723227086     0.004329977

Is there any way to achieve this in Excel without using one of the following methods:

• Adding columns to the source data to perform the 500/FX calculation before pivot
• Adding calculations outside of the pivot using GETPIVOTDATA()
• Using VBA