Inconsistent results in Excel pivot table difference calculation

[antoine1]

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

 

[prasaddn]

Hi Antoine,


Unfortunately, I am not able to download the file, do you mind sharing the data points on ths site if its not too big?


I took some random data and tried to replicate your situation. The difference seems to be due to comparision of base year's month on month and not really amt / qty. May be I am wrong as I have not seen your data.


BTW, you will find solution in this forum, for sure.


Regards,

Prasad

 

[antoine1]

Thank you Prasad for your answer!

Here is the data I used in the file, my problem originated in a much bigger file but I could replicate it with this data^


Date Quantity Amount

01-Jan-10 5 200

01-Feb-10 12 600

01-Jan-11 20 1200

01-Feb-11 6 420


Best regards,

Antoine

 

[antoine1]

Hello again Prasad,

I have tried to reformat a bit my pivot table so you can see what I was trying to do^


Years Date

2011 Total 2011 2010 Total 2010

Datos Jan Feb Jan Feb

Qty 20 6 26 5 12 17

Amt 1,200 420 1,620 200 600 800

Diff Q 300% -50% 53%

Diff A 500% -30% 103%


If you build it this way, you will see the correct 103%. Then drag the Data field under the dates, you will get one row of data, but the 103% will become 9429%.


Thanks for the help!

Antoine

 

[NARAYANK991]

Hi antoine1 ,


If you see the Field List of the two pivot tables , the difference is :


1. In the correct version , the Values Field appears under Row Labels


2. In the wrong version , the Values Field appears under Column Labels


Just dragging the Values Field between the two options , keeps changing the Pivot Table between the two versions.


However , if you drag the Values Field to the topmost position under Column Labels , above Años and Date , then it gives the correct result. Positioning it wrongly , gives the wrong result.


Narayan

 

[antoine1]

Hello Narayan,

I agree, but this is the reason I post this question. I would have thought there were no "right" or "wrong" positions in a pivot table. I would like to display the %difference right next to the values. In my real workbook I have monthly data, and with the values on top the %s are far away, offscreen on the right.

Best,

Antoine