Solved

Help with Pivot Table

Posted on 2014-09-04
2
80 Views
Last Modified: 2014-10-15
Hello,

I am connecting to SharePoint list to excel and downloaded data for a report that i am attempting to created.

Data that i am using from SharePoint list is  project team, project name, original project completion date, and Actual project completion date.

I have created calculated field that subtracts original project completion date from Actual project completion date which gives me variance. I have also added project team as filter so i can view result for each team.

Here is issue that i don't know how to solve:

- I like grand total to show average of delay in project rather than total delay by project team.
- I like to see how many how many % of projects are completing with only 2 days or less variance.
- I like to see this data for each quarter.

The reason i like to use pivot table is so i can just refresh the data.

Please help.

Thanks
0
Comment
Question by:dharmeshchavda
2 Comments
 
LVL 22

Expert Comment

by:yo_bee
ID: 40306212
it would be helpful if you attached the excel file to this thread
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40306405
You don't need a pivot table to return these results.  If you're using Excel 2007 or newer, you can use a combination of SUMIFS, COUNTIFS, COUNTIF functions to return these results from your table.

If you can set up your project table like so:
sample data tablewhere you have the Delay/Variance calculated and a lookup that determines the quarter (I'm using the Original Date as the lookup value here), then you can set up a reporting sheet that provides the data you need.  I've created a table on a sheet named "Data" that is referred to in the formulas below.

- I like grand total to show average of delay in project rather than total delay by project team.
Set up a table on a new sheet with all team names in one column (example here starts in cell A4).
B4: =SUMIFS(Data!$F$2:$F$16,Data!$A$2:$A$16,A4)/COUNTIF(Data!$A$2:$A$16,A4)  <-full year (see below for qtr)

- I like to see how many how many % of projects are completing with only 2 days or less variance.
=COUNTIF(Data!$F$2:$F$16,"<=2")/COUNTA(Data!$B$2:$B$16)

- I like to see this data for each quarter.
You set up a new sheet for quarterly reporting.  Add a cell (say, cell A1 and use data validation to point to your quarterly labels) that all formulas will refer to in order to report the correct quarter. Then you create modified versions of the above formulas to further limit the results to that quarter.

-Average delay:
=IFERROR(SUMIFS(Data!$F$2:$F$16,Data!$A$2:$A$16,A4,Data!$C$2:$C$16,$A$1)/COUNTIFS(Data!$A$2:$A$16,A4,Data!$C$2:$C$16,$A$1),0)

% with <= 2 days variance
=COUNTIFS(Data!$F$2:$F$16,"<=2",Data!$C$2:$C$16,$A$1)/COUNTIF(Data!C2:C16,$A$1)

I've created an example workbook that demonstrates all of these functions.  Let me know if you have any questions.

Regards,
-Glenn
EE-ProjectPivot.xlsx
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

765 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