Solved

# Help with Pivot Table

Posted on 2014-09-04
82 Views
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.

Thanks
0
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

LVL 23

Expert Comment

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

LVL 27

Accepted Solution

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:
where 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

Question has a verified solution.

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

### Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll