Lidiane has attended:
Excel Intermediate course
Excel Advanced course
Formula in Pivot table
Hi,
I am trying to create a pivot for a headcount analysis for our business and I have done it now, however I am trying to see the movement between one month to the other and the cumulative figures for the year but cannot find the correct formula to add in.
I was wondering if I could e-mail you the spread sheet to have a look what I have done so far and see if you can help me?
Thanks in advance for your help.
kind regards,
Lidi
My e-mail is lvacari@momentagroup.com
RE: Formula in Pivot table
Hello Lidi,
Thank you for your question. You can send the spreadsheet to the following email address: forum@stl-training.co.uk. We will have a look at it and suggest some solutions.
Kind regards
Marius Barnard
Excel Trainer
RE: Formula in Pivot table
Hi Lidi,
Having looked at your sheet, to show the movement from one month to the next, in the Summary sheet, right-click on one of your monthly figures, then select Show Values As, and then Difference From. A new box opens, where you have to set the Base Field to As At. Leave the second option on (previous). Click Yes in the next box. This will show the figures as movement from the previous period.
For the yearly cumulative figures, I would suggest building a new Pivot Table from the Raw Data. In the new Pivot Table, drag 'As at' to the Columns field, 'Client' to the Rows field and Difference to the Values field. The cumulative totals will show in the Grand Totals column.
I hope this is helpful.
Kind regards
Marius Barnard
Excel Trainer
RE: Formula in Pivot table
Thanks Marius for your help
The movement did work. however the cumulative did not as the difference on the raw tab needs to be changed.
I would like to build a formula as count the client for as specific date less another date.
Is that possible?
Thanks,
Lidi
RE: Formula in Pivot table
Hi Lidi,
If you could adjust your Pivot Table to have only one item in the Values field, with the dates still showing in columns, you can insert a Calculated Item. You need to select one of the date headers in your Pivot Table, then in the Options ribbon, click on 'Fields, Items and Sets'. Then select Calculated Item. A box opens where you can build formulas using the existing columns in your Pivot Table. E.g. you can build a formula which subtracts one date column from another. The calculated items will show as new columns in your Pivot Table.
Once you have created some Calculated Items, you can show the results as a count rather than a sum by right-clicking on a value, then summarising values as Count.
I hope this helps. If it doesn't, I'll ask my colleagues for more ideas.
Kind regards
Marius
