Personal computing discussed

Moderators: SecretSquirrel, just brew it!

 
druidcent
Minister of Gerbil Affairs
Topic Author
Posts: 2213
Joined: Wed Aug 07, 2002 7:55 pm
Location: Earth, Sol, Milky Way
Contact:

Excel Pivot Table help

Fri Jan 18, 2013 2:12 pm

Hi all,

I'm pretty decent with excel, but I'm running into a problem that is stumping me right now.

I am trying to determine bug fix rates for my team. I've got two sets of data, Set A= count of bugs opened per day, Set B= count of bugs closed per day

I want a chart showing for a given day, the number of bugs opened and closed for a given team member.

I'm trying to use a Pivot table, but the problem is that unless I manually group the dates, I don't see the counts correctly. Right now, I can easily see the breakdown for either open or close, but not both for the same day.

I've got the sets laid out side by side (columns A-C is set A, columns D-F is set B). The pivot table reads across the entire row, so if I select a date from Set A, then it will only count Set B if it is on the same row, but it ignores the date in set B. If I try and filter by set B, it shows the date in Set B, but still counts it for the date on set A.

I guess what I'm trying to do is align the number of bugs opened and closed for a given date, and then break it out by team member. I can do it in two pivot tables, but then how do I chart it on the same graph?
 
JustAnEngineer
Gold subscriber
Gerbil God
Posts: 16842
Joined: Sat Jan 26, 2002 7:00 pm
Location: The Heart of Dixie

Re: Excel Pivot Table help

Fri Jan 18, 2013 8:01 pm

Rather than fixing your problem in the pivot table generated from the two data tables, I might be tempted to create a third data table or modify one of the existing ones to automatically blend the two existing data sets. You can use the VLOOKUP() function to pull data from the existing data table that matches the date in your new table.
http://www.techonthenet.com/excel/formulas/vlookup.php
i7-6700K, NT06-Pro, GA-Z170N-Gaming5, 32 GiB, 980Ti, SM951, 5TB HDD, Blu-ray, FTZ01, SX600-G, 3007WFP+2001FP, RK-9000BR, MX518
 
druidcent
Minister of Gerbil Affairs
Topic Author
Posts: 2213
Joined: Wed Aug 07, 2002 7:55 pm
Location: Earth, Sol, Milky Way
Contact:

Re: Excel Pivot Table help

Fri Jan 18, 2013 8:06 pm

That's a good idea.. question though, VLookup returns the first cell that it finds, so if I've got multiple dates how would that match up? Also, I'm not really trying to blend the data.. what I'm looking for is a way to show for a given date, how many bugs a team member closed, and the how many bugs were filed against them.
 
Usacomp2k3
Gerbil God
Posts: 21401
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL
Contact:

Re: Excel Pivot Table help

Sun Jan 20, 2013 11:15 pm

Sumifs() and countifs() are your friend. I'd agree with the 3rd table. That's how I'd do it myself.

Who is online

Users browsing this forum: No registered users and 1 guest