How to group bars showing counts and overlay line compliance chart
Using data similar to what's below (month, ticket count type, customer, SLA
compliance %), I'd like to be able to have the bars with the # of tickets opened and closed for each month grouped so the bars touch each other while still having space inbetween each months' bars (using the 1st axis). I only can seem to find an option that determines the space between all bars...not allowing me to group the bars for a given month. As a second issue, the SLA compliance % data for closed tickets won't show up as a line chart overlaying the bar chart described above. I can get milestones to show, but not the line itself (using the 2nd axis). Any help in accomplishing either or both items would be greatly appreciated. Ticket Volume & SLA Compliance Month Ticket Count Types Customer #1 Customer #2 Customer #3 Resolution SLA Jul Opened 349 53 426 Closed 340 60 430 68% Aug Opened 405 69 477 Closed 390 75 460 73% Sep Opened 430 90 732 Closed 450 90 715 78% Oct Opened 408 79 621 Closed 400 70 600 69% |
How to group bars showing counts and overlay line compliance chart
Are you looking for a stacked/clustered column chart? Look he
http://peltiertech.com/Excel/ChartsH...sterStack.html To make the line segments appear, go to Tools men Options Chart, and under Plot Empty Cells As, choose Interpolate. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "JaWeek" wrote in message ... Using data similar to what's below (month, ticket count type, customer, SLA compliance %), I'd like to be able to have the bars with the # of tickets opened and closed for each month grouped so the bars touch each other while still having space inbetween each months' bars (using the 1st axis). I only can seem to find an option that determines the space between all bars...not allowing me to group the bars for a given month. As a second issue, the SLA compliance % data for closed tickets won't show up as a line chart overlaying the bar chart described above. I can get milestones to show, but not the line itself (using the 2nd axis). Any help in accomplishing either or both items would be greatly appreciated. Ticket Volume & SLA Compliance Month Ticket Count Types Customer #1 Customer #2 Customer #3 Resolution SLA Jul Opened 349 53 426 Closed 340 60 430 68% Aug Opened 405 69 477 Closed 390 75 460 73% Sep Opened 430 90 732 Closed 450 90 715 78% Oct Opened 408 79 621 Closed 400 70 600 69% |
How to group bars showing counts and overlay line compliance c
Hi Jon €“
I really appreciate your response! For everything Im trying to accomplish I really need to utilize the 2nd axis instead of hiding it. I got the chart to group different counts for each month by adding an empty row of cells after each months data. For some reason I am currently unable to get the SLA % line to show in the chart€¦I can get the milestones to show but thats not as effective as the line. I forwarded you my example file. Can you can take a look at it and tell me if you know of a way to make the SLA % line visible? To summarize what Im trying to accomplish in a single chart: €¢ Group the number/count of opened and number of closed tickets for each month (as bar chart format), and €¢ Overlay percentage of tickets resolved within SLA over the volume for each month (as line chart format) o SLA is only calculated for Closed Tickets (1 of the 2 counts associated with each month) Thanks again for your response and help! Jason ==================================== "Jon Peltier" wrote: Are you looking for a stacked/clustered column chart? Look he http://peltiertech.com/Excel/ChartsH...sterStack.html To make the line segments appear, go to Tools men Options Chart, and under Plot Empty Cells As, choose Interpolate. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "JaWeek" wrote in message ... Using data similar to what's below (month, ticket count type, customer, SLA compliance %), I'd like to be able to have the bars with the # of tickets opened and closed for each month grouped so the bars touch each other while still having space inbetween each months' bars (using the 1st axis). I only can seem to find an option that determines the space between all bars...not allowing me to group the bars for a given month. As a second issue, the SLA compliance % data for closed tickets won't show up as a line chart overlaying the bar chart described above. I can get milestones to show, but not the line itself (using the 2nd axis). Any help in accomplishing either or both items would be greatly appreciated. Ticket Volume & SLA Compliance Month Ticket Count Types Customer #1 Customer #2 Customer #3 Resolution SLA Jul Opened 349 53 426 Closed 340 60 430 68% Aug Opened 405 69 477 Closed 390 75 460 73% Sep Opened 430 90 732 Closed 450 90 715 78% Oct Opened 408 79 621 Closed 400 70 600 69% |
How to group bars showing counts and overlay line compliance c
Jason needed to interpolate where there were blank cells, but Excel doesn't
offer that for a combination chart, only for a line or XY chart. The trick is to either change all the series to Line, set the option (Tools menu Options Chart tab in Excel 2003 or Ribbon: Chart Tools Design tab Data Group Select Data, Hidden and Empty Cells button in Excel 2007), then change the series back, or fill the blank cells with #N/A to make Excel draw the lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "JaWeek" wrote in message ... Hi Jon - I really appreciate your response! For everything I'm trying to accomplish I really need to utilize the 2nd axis instead of hiding it. I got the chart to group different counts for each month by adding an empty row of cells after each month's data. For some reason I am currently unable to get the SLA % line to show in the chart.I can get the milestones to show but that's not as effective as the line. I forwarded you my example file. Can you can take a look at it and tell me if you know of a way to make the SLA % line visible? To summarize what I'm trying to accomplish in a single chart: . Group the number/count of opened and number of closed tickets for each month (as bar chart format), and . Overlay percentage of tickets resolved within SLA over the volume for each month (as line chart format) o SLA is only calculated for Closed Tickets (1 of the 2 counts associated with each month) Thanks again for your response and help! Jason ==================================== "Jon Peltier" wrote: Are you looking for a stacked/clustered column chart? Look he http://peltiertech.com/Excel/ChartsH...sterStack.html To make the line segments appear, go to Tools men Options Chart, and under Plot Empty Cells As, choose Interpolate. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "JaWeek" wrote in message ... Using data similar to what's below (month, ticket count type, customer, SLA compliance %), I'd like to be able to have the bars with the # of tickets opened and closed for each month grouped so the bars touch each other while still having space inbetween each months' bars (using the 1st axis). I only can seem to find an option that determines the space between all bars...not allowing me to group the bars for a given month. As a second issue, the SLA compliance % data for closed tickets won't show up as a line chart overlaying the bar chart described above. I can get milestones to show, but not the line itself (using the 2nd axis). Any help in accomplishing either or both items would be greatly appreciated. Ticket Volume & SLA Compliance Month Ticket Count Types Customer #1 Customer #2 Customer #3 Resolution SLA Jul Opened 349 53 426 Closed 340 60 430 68% Aug Opened 405 69 477 Closed 390 75 460 73% Sep Opened 430 90 732 Closed 450 90 715 78% Oct Opened 408 79 621 Closed 400 70 600 69% |
How to group bars showing counts and overlay line compliance c
Thanks for figuring out and sharing the solution to my problem!
"Jon Peltier" wrote: Jason needed to interpolate where there were blank cells, but Excel doesn't offer that for a combination chart, only for a line or XY chart. The trick is to either change all the series to Line, set the option (Tools menu Options Chart tab in Excel 2003 or Ribbon: Chart Tools Design tab Data Group Select Data, Hidden and Empty Cells button in Excel 2007), then change the series back, or fill the blank cells with #N/A to make Excel draw the lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "JaWeek" wrote in message ... Hi Jon - I really appreciate your response! For everything I'm trying to accomplish I really need to utilize the 2nd axis instead of hiding it. I got the chart to group different counts for each month by adding an empty row of cells after each month's data. For some reason I am currently unable to get the SLA % line to show in the chart.I can get the milestones to show but that's not as effective as the line. I forwarded you my example file. Can you can take a look at it and tell me if you know of a way to make the SLA % line visible? To summarize what I'm trying to accomplish in a single chart: . Group the number/count of opened and number of closed tickets for each month (as bar chart format), and . Overlay percentage of tickets resolved within SLA over the volume for each month (as line chart format) o SLA is only calculated for Closed Tickets (1 of the 2 counts associated with each month) Thanks again for your response and help! Jason ==================================== "Jon Peltier" wrote: Are you looking for a stacked/clustered column chart? Look he http://peltiertech.com/Excel/ChartsH...sterStack.html To make the line segments appear, go to Tools men Options Chart, and under Plot Empty Cells As, choose Interpolate. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "JaWeek" wrote in message ... Using data similar to what's below (month, ticket count type, customer, SLA compliance %), I'd like to be able to have the bars with the # of tickets opened and closed for each month grouped so the bars touch each other while still having space inbetween each months' bars (using the 1st axis). I only can seem to find an option that determines the space between all bars...not allowing me to group the bars for a given month. As a second issue, the SLA compliance % data for closed tickets won't show up as a line chart overlaying the bar chart described above. I can get milestones to show, but not the line itself (using the 2nd axis). Any help in accomplishing either or both items would be greatly appreciated. Ticket Volume & SLA Compliance Month Ticket Count Types Customer #1 Customer #2 Customer #3 Resolution SLA Jul Opened 349 53 426 Closed 340 60 430 68% Aug Opened 405 69 477 Closed 390 75 460 73% Sep Opened 430 90 732 Closed 450 90 715 78% Oct Opened 408 79 621 Closed 400 70 600 69% |
All times are GMT +1. The time now is 11:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com