Personal computing discussed

Moderators: SecretSquirrel, just brew it!

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

Excel Pivot Table help

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: 17567
Joined: Sat Jan 26, 2002 7:00 pm
Location: The Heart of Dixie

Re: Excel Pivot Table help

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, RX Vega56, SM951, 5TB HDD, Blu-ray, FTZ01, SX600-G, C32HG70, RK-9000BR, MX518

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

Re: Excel Pivot Table help

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: 21546
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL
Contact:

Re: Excel Pivot Table help

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