If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rearranging data on a stacked column chart
I use a stacked column chart to show the total number of hours worked each
week per project. Because we are managing many projects that only require 2-4 hours of work per week, they do not show up very well on the stacked column chart. I would like to take the projects with 5 hours or less and combine them into one entry in each column to make it easier to read. Can this be done??? |
#2
|
|||
|
|||
Anything can be done. The trick is doing it with a minimum of pain. This
way takes a few minutes, but works nicely. I think I would make a duplicate range of data, linked to the main set with formulas. say the original data is in B2:F10 (appropriate labels in column A and row 1). Select B12:F20 with B12 as the active cell. Enter this formula: =IF(B25,B2,0) Hold CTRL while pressing Enter, and the formula will be repeated in the entire range. If each week's data is in a column, enter the label "Other" in A21, select B21, and type this formula: =SUM(IF(B2:B10=5,B2:B10,0)) Hold CTRL+SHIFT while pressing Enter, and Excel will turn this into an array formula, and surround it with curly brackets, like this: {=SUM(IF(B2:B10=5,B2:B10,0))} Copy this and past it into C21:F21. If each week's data is in a row, select G12, and CTRL+SHIFT+ENTER this array formula: {=SUM(IF(B2:F2=5,B2:F2,0))} (remember, don't type the brackets, that's Excel's job), and paste it into G13:G20. Now plot this entire range. Any series (projects) with less than 5 hours in a week won't show up in that week's stack, but will be combined into "Other". - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Stretch wrote: I use a stacked column chart to show the total number of hours worked each week per project. Because we are managing many projects that only require 2-4 hours of work per week, they do not show up very well on the stacked column chart. I would like to take the projects with 5 hours or less and combine them into one entry in each column to make it easier to read. Can this be done??? |
#3
|
|||
|
|||
Thanks Jon - I'll give it a try...
"Jon Peltier" wrote: Anything can be done. The trick is doing it with a minimum of pain. This way takes a few minutes, but works nicely. I think I would make a duplicate range of data, linked to the main set with formulas. say the original data is in B2:F10 (appropriate labels in column A and row 1). Select B12:F20 with B12 as the active cell. Enter this formula: =IF(B25,B2,0) Hold CTRL while pressing Enter, and the formula will be repeated in the entire range. If each week's data is in a column, enter the label "Other" in A21, select B21, and type this formula: =SUM(IF(B2:B10=5,B2:B10,0)) Hold CTRL+SHIFT while pressing Enter, and Excel will turn this into an array formula, and surround it with curly brackets, like this: {=SUM(IF(B2:B10=5,B2:B10,0))} Copy this and past it into C21:F21. If each week's data is in a row, select G12, and CTRL+SHIFT+ENTER this array formula: {=SUM(IF(B2:F2=5,B2:F2,0))} (remember, don't type the brackets, that's Excel's job), and paste it into G13:G20. Now plot this entire range. Any series (projects) with less than 5 hours in a week won't show up in that week's stack, but will be combined into "Other". - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Stretch wrote: I use a stacked column chart to show the total number of hours worked each week per project. Because we are managing many projects that only require 2-4 hours of work per week, they do not show up very well on the stacked column chart. I would like to take the projects with 5 hours or less and combine them into one entry in each column to make it easier to read. Can this be done??? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a stacked column chart with 2 axes. | David Hall | General Discussion | 1 | July 10th, 2004 08:12 PM |
Countif with 2 or more data ranges in same column | Doug | Worksheet Functions | 1 | July 4th, 2004 08:57 AM |
Multiple Series of Series in Stacked Column chart? | nlee144(NoSpamTakeSquareRootOfNumber) | Charts and Charting | 2 | June 5th, 2004 03:31 AM |
Stacked Column Chart with | Holger Gerths | Charts and Charting | 1 | May 15th, 2004 01:35 AM |
can I hide a series in a stacked bar chart but keep the series in the data table | Stephen Bullen | Charts and Charting | 0 | December 5th, 2003 10:53 AM |