1
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
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