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.
