[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Create a variance column in a Pivot Table - Excel 2010

Posted on 2011-10-24
12
Medium Priority
?
836 Views
Last Modified: 2012-06-27
This seems to simple, but I have a pivot table that will only hold the data for two months, that is the end of the month data for say, 8/31/2011 and a column for 9/30/2011.  All I want is to add a third column with the variance between the two, but this seems impossbile in Excel 2010.  
0
Comment
Question by:ssmith94015
  • 8
  • 4
12 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37019004
This should give you all the information needed.  If you have any questions after reading the article just post again.


MS Excel 2010 custom calc on pivot tables
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37019047
A little more detail on the above solution.

You'll need to add the same data field twice.  The first one you leave as normal.

The second one you'll adjust as per this image.

 Image of how to do it.
0
 

Author Comment

by:ssmith94015
ID: 37019106
Ok, I think that is where I am getting stuck, adding the same field twice. Variance Variance
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:ssmith94015
ID: 37019109
Sorrry, I added it twice.
0
 

Author Comment

by:ssmith94015
ID: 37019116
I keep getting N/A in both columns now.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37019241
Ok here is what I did, and it worked.

Start your pivot table as normal
Whatever you are summing for your data that's the field you want to enter 2x.

The first time you want to enter it as normal and set value to sum or whatever

The second time you want to change the field value, and click on the show values tab and choose previous.  

I've posted something that you can look at as an Excel file.
 Variance-in-PivotTable-Excel-201.xlsx
0
 

Author Comment

by:ssmith94015
ID: 37019277
ScriptAddict, let me try your suggestion.  I have a meeting so it will be a bit before I can return to this.
0
 

Author Comment

by:ssmith94015
ID: 37019340
Ok, I have the second column, but it is giving me the difference of the value in the same column.  That is, the (9,711.00) value is the difference between 65,797 and 56,086 in the first few rows.  I need teh different between 65,797 and 63,055.
 Variance
0
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 2000 total points
ID: 37019385
So close!  We've almost got it!  

Go back to where you selected previous on the second column.  

You need to change your base field to whatever your month column is called.  
I think it is on whatever your item column is named.

When you are there you might want to change the Custom name to more accurately describe the field.  ie. Variance from Prior Month Sales
0
 

Author Comment

by:ssmith94015
ID: 37019651
Works, below is the final version!  Now, if I can just remember how I did it for the next time!  Also, need to code this all, but at least i is possible.  I as getting concerned that it could not be done!  Thank you.. Variance
0
 

Author Closing Comment

by:ssmith94015
ID: 37019655
Now to get this all coded in VBA!

Sandra
0
 

Author Comment

by:ssmith94015
ID: 37019667
Ok, showed the user and now they also want a percentage variance column.  If I have problems, will post a related question.

Sandra
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question