Conditional Data Bar Formatting

[EDBD]

Hi MVPs,

I'm stuggleing with formatting an excel pivot table correctly in excel 2007.
Here is what i've got, a pivot table with hours as the values as compared to
the jobsites they are logged at. here is my struggle. I am trying to make
excel display the the chart with data bars so that i know when a jobsite is
on schedule, and when too many hours (compared to the average) are being
spent there. for example at jobsite A, i want the bar to fill in blue until
the expected number of hours is reached. If the actual number of hours spent
increases over the expected number, i want it to begin a different data bar
in red showing how far over expected hours Job A is compared to say job B, C,
D, etc.
the data i am using comes from is a seperate excel table with the colums
EMPLOYEE, JOBSITE, COST CODE, HOURS, DATE.

 

[Shane Devenshire]

Hi,

You are using the term Data Bars and then you are using the term Chart.
Data Bars fill cells and are not on charts. If you are talking about a bar
or column chart in which the bars change color after they reach a certain
value. Consider a stacked column or bar and then use formulas in the data
are to show hours up to some cutoff as one series and hours over that limit
as a second series. In other words you need to create two additional
columns in your data area which breaks up the HOURS numbers into Average
hours (whatever that is) and Excess hours.

For example
A B
C
1 Hours Target Hours Excess Hours
2 58 =MIN(AVERAGE(Hours),A2) =A2-B2

Cheers,
Shane Devenshire
Microsoft Excel MVP