Hi Chandoo,
I found your site a couple of days ago: thank you, I've learned many, many things !
I posted a question similar to the following one on MrExcel a couple of months ago, but got no reply. Maybe you can help ! Please refer to the file at https://rapidshare.com/files/2127656999/Wrong_percentage_in_second_field.xls
1) With the data in A1:C5 I built the first pivot table. After displaying Qty and Amt, I grouped the dates by month and year, and sorted the years descending.
2) I added new copies of Qty and Amt, but this time displayed them as % difference with Year as base field, and refered them to year 2010.
3) Finally I configured the Years field to have SubTotals.
The result is the table at A9, and the % difference for the Amount in 2011 is correct: 103% (I painted it red on the spreadsheet).
I now drag the Data field under the dates so the numbers are spread out horinzontally (in the example file I duplicated the table at A18 to make this more clear):
now take a look at the Diff Amt for year 2011, and it's wrong: 9429%.
Instead of calculating (Amt11 / Amt10) -1, it seems Excel is doing (Amt11 / Qty10) -1
I did this on Excel 2003 in spanish. I hace checked these results on another PC with 2007 and have the same results. Can you see what is wrong ?
Of course if I drag back the data to the original position, the 103% is correct again...
Thanks for any help!!
Antoine
I found your site a couple of days ago: thank you, I've learned many, many things !
I posted a question similar to the following one on MrExcel a couple of months ago, but got no reply. Maybe you can help ! Please refer to the file at https://rapidshare.com/files/2127656999/Wrong_percentage_in_second_field.xls
1) With the data in A1:C5 I built the first pivot table. After displaying Qty and Amt, I grouped the dates by month and year, and sorted the years descending.
2) I added new copies of Qty and Amt, but this time displayed them as % difference with Year as base field, and refered them to year 2010.
3) Finally I configured the Years field to have SubTotals.
The result is the table at A9, and the % difference for the Amount in 2011 is correct: 103% (I painted it red on the spreadsheet).
I now drag the Data field under the dates so the numbers are spread out horinzontally (in the example file I duplicated the table at A18 to make this more clear):
now take a look at the Diff Amt for year 2011, and it's wrong: 9429%.
Instead of calculating (Amt11 / Amt10) -1, it seems Excel is doing (Amt11 / Qty10) -1
I did this on Excel 2003 in spanish. I hace checked these results on another PC with 2007 and have the same results. Can you see what is wrong ?
Of course if I drag back the data to the original position, the 103% is correct again...
Thanks for any help!!
Antoine