?
Solved

Help with Pivot Table

Posted on 2014-09-04
2
Medium Priority
?
85 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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 article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 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