- Joined
- Mar 28, 2013
- Messages
- 1
- Reaction score
- 0
Hi,
In Excel 2010 I have some bug data which looks something like the following, pasted directly from our bug tracking software:
Bug no.-Priority ----Date Raised Week no. (added myself, see below)
001 ------ Low ----- 07.02.2013-------6
002 ------ High ----- 11.02.2013-------7
003 ------ Medium --12.02.2013-------7
etc.
I've added a WEEKNUM column so that each 'date raised' entry will yield a week number.
I've successfully created a pivot table that shows the number of bugs raised per week, split out by priority. It has the week numbers along the x axis and no. of bugs along the y. Each week's column is a stack split by priority.
What I'd like to add now is a trendline for the total bugs each week.
Of course the stack means I can't, it treats each bug priority as a series.
Googling a solution the advice is to create a 'total' column in the data so you can add it as a series and trend that. However, my total of the different categories of bugs, and overall totals per week, was extrapolated by the pivot table, it isn't a column of numbers that I can somehow get the pivot table to pick up from the source data given the above source data format.
The only way I could see I could do it would be to have cells in a new column on the end of the source data I've pasted that contained some formula that said 'count as 1 all the cells in the Week No. column that contain a specific value and total it here', for each week.
If that's the case, what would the formula look like, or is there another way to do this?
Thanks.
In Excel 2010 I have some bug data which looks something like the following, pasted directly from our bug tracking software:
Bug no.-Priority ----Date Raised Week no. (added myself, see below)
001 ------ Low ----- 07.02.2013-------6
002 ------ High ----- 11.02.2013-------7
003 ------ Medium --12.02.2013-------7
etc.
I've added a WEEKNUM column so that each 'date raised' entry will yield a week number.
I've successfully created a pivot table that shows the number of bugs raised per week, split out by priority. It has the week numbers along the x axis and no. of bugs along the y. Each week's column is a stack split by priority.
What I'd like to add now is a trendline for the total bugs each week.
Of course the stack means I can't, it treats each bug priority as a series.
Googling a solution the advice is to create a 'total' column in the data so you can add it as a series and trend that. However, my total of the different categories of bugs, and overall totals per week, was extrapolated by the pivot table, it isn't a column of numbers that I can somehow get the pivot table to pick up from the source data given the above source data format.
The only way I could see I could do it would be to have cells in a new column on the end of the source data I've pasted that contained some formula that said 'count as 1 all the cells in the Week No. column that contain a specific value and total it here', for each week.
If that's the case, what would the formula look like, or is there another way to do this?
Thanks.
Last edited: