Rearranging data on a stacked column chart

[Stretch]

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

 

[Jon Peltier]

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(B2>5,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/
_______