Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

Help with Pivot Table

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
dharmeshchavda
Asked:
dharmeshchavda
1 Solution
 
yo_beeDirector of ITCommented:
it would be helpful if you attached the excel file to this thread
0
 
Glenn RayExcel VBA DeveloperCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now