TR Forums

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?

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

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.

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