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???

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???

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???