Pivot table to calculate variance showing budget and actual.
in [Functions]
|
From: YY san. on 19 Apr 2010 12:45 Hi, I know this is rather simple, but I just could not get it right. I have this data: Expenese Type Amt Scenario Staff cost 200 Budget Admin cost 75 Budget Office rent 5000 Budget Staff cost 100 Actual Admin cost 45 Actual Office rent 300 Actual I am able to get the difference using the pivot table "Difference from", but it only showed that single column. I would like my pivot table to show: Expenese Type Budget Actual Variance Staff cost 200 100 100 Admin cost 75 45 30 Office rent 5000 300 4700 Appreciate all guidance and hints. Thanks!
From: trip_to_tokyo on 19 Apr 2010 13:11 EXCEL 2007 I have just put up a file for you at:- http://www.pierrefondes.com/ Item number 95 (towards the top of my home page at the time of posting). This appears to give you what you want. If you agree please hit Yes. Thanks. "YY san." wrote: > Hi, > I know this is rather simple, but I just could not get it right. I have this > data: > > Expenese Type Amt Scenario > Staff cost 200 Budget > Admin cost 75 Budget > Office rent 5000 Budget > Staff cost 100 Actual > Admin cost 45 Actual > Office rent 300 Actual > I am able to get the difference using the pivot table "Difference from", but > it only showed that single column. I would like my pivot table to show: > > Expenese Type Budget Actual Variance > Staff cost 200 100 100 > Admin cost 75 45 30 > Office rent 5000 300 4700 > > Appreciate all guidance and hints. > Thanks!
From: Ashish Mathur on 22 Apr 2010 00:15 Hi, You should convert the items under the Scenario column I.e. Budget and Actual to columns instead. After the restructuring, there would be 4 columns in your source data - Expenses type, Amt, Base and Actual. Now create a pivot from this and then you may write a calculated field -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "YY san." <YYsan(a)discussions.microsoft.com> wrote in message news:1A6FBDA8-0536-4FEA-9468-7FA526572627(a)microsoft.com... > Hi, > I know this is rather simple, but I just could not get it right. I have > this > data: > > Expenese Type Amt Scenario > Staff cost 200 Budget > Admin cost 75 Budget > Office rent 5000 Budget > Staff cost 100 Actual > Admin cost 45 Actual > Office rent 300 Actual > I am able to get the difference using the pivot table "Difference from", > but > it only showed that single column. I would like my pivot table to show: > > Expenese Type Budget Actual Variance > Staff cost 200 100 100 > Admin cost 75 45 30 > Office rent 5000 300 4700 > > Appreciate all guidance and hints. > Thanks!
|
Pages: 1 Prev: Text from Referenced cell truncated Next: Custom Functions Available for all Workbooks |