Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table analysis
Hi Guys,
I am trying to prepare a pivot table wherein I am not sure where I am missing my eyeballs ;) The first tab sheet contains data as below. SBU, Domain, project,Emp#, Empname, exp in years, exp in months, exp total, range S1,D1,P1,E1,N1,8,11,8.92,4+ Yrs S1,D1,P1,E2,N2,6,7,6.58,4+ Yrs S1,D1,P2,E3,N3,3,7,3.58,2-4 Yrs S1,D1,P2,E4,N4,2,7,2.58,2-4 Yrs NOTE: 1. the data is delimited by comma. 2. all the data is pulled from SAP BW 3. the experience is captured in years and in months 4. formula for the column 'exp total' is defined as (F2*12+G2)/12; where F2 = 'exp in years', G2 = 'exp in months' 5. formula for range column is defined as: IF(H24,"4+ Yrs",IF(H22,"2-4 Yrs", "0-2 Yrs")) The pivot table is created with the range and the layout is defined as: ROW = SBU, DOMAIN, PROJECT COLUMN = RANGE DATE = COUNT OF RANGE The Pivot table gets created without any problem. Till here everything is fine. Now the requirement is to provide avegrage years of experience per project in the column. The formula will be total of 'exp total' / number of employees per project. This means, for Project P1 the avg years of exp will be (8.92 + 6.58) / 2 = 7.75 Is there a way where I could add this information also? This is the output which I am looking for ------------------------------------------------------------- Count of range range SBU Domain project 2-4 Yrs 4+ Yrs Avg Years S1 D1 P1 2 7.75 P2 2 3.09 D1 Total 2 2 S1 Total 2 2 ------------------------------------------------------------- Any help and advise is appreciated. Thank you in advance, Anu |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
#Value in Analysis Services Pivot Table | Excel Discussion (Misc queries) | |||
How do I build a sensitivity analysis table? | Excel Worksheet Functions | |||
Pivot table and customer analysis | Excel Worksheet Functions | |||
Pivot Table Subtotals/Variance Analysis | Excel Discussion (Misc queries) |