MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need some chart help


Posted by Lisa on November 07, 2001 4:53 AM

Hi! I am making a column chart that shows the number of hours employees have worked in a day.

I have the data divided into Productive hours and Non productive hours and there there is another set of data that would give a code for non productive hours.

What I would like to see is the Productive hours go above the category axis and the non productive hour be a column that drops down below the category axis.

Right now both catagories go above the axis but are just different colors.

How do I do this?

Is there a way to get the code to correspond with the non productive column. For example, say the pro hours are blue columns and the non pro are yellow columnd. Is there a way to have the yellow colums also have the code on them?

THX!


Posted by Dan on November 07, 2001 5:25 AM

Would this work?
When you formulate "NonProductive" hours, you can calculate them to be negative numbers. Then in your chart you can format the "Hours" axis as a "Number" with 0 decimal places and show negative numbers as red (as in accounting).

Posted by Lisa on November 07, 2001 5:33 AM

Thanks I will try it!

Posted by Lisa on November 07, 2001 5:39 AM

Assuming that your numbers are in A1:A7 (otherwise adjust):

=SUMIF(A1:A7,">0")/MAX(1,COUNTIF(A1:A7,">0"))

Aladin

Posted by Lisa on November 07, 2001 5:44 AM

Dan that works perfectly!!!! Thank you! The only problem I can see is that I have a totals at the end and for some reason the boss wants them to total a positive number for 80 hour work week! Why? I don't know because I think non productive work hours should count as a negative anyway because you didn't work those hours. ???

Posted by Dan on November 07, 2001 5:56 AM

How about making a separate column with negative numbers just for the chart and a column with positive numbers for your totals?

OR:

In your totals formula you can use the ABS (absolute value) function. i.e - instead of "=A1+B1" use "=A1+ABS(B1)" where B1 is your negative "nonproductive" number. That would work well also.