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
dharmeshchavdaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.