Help with Formula in Staffing Model

[esi]

I have a staffing model which tells me by month how may employees I need for
our production line. I'm trying to setup a formula to identify the months
we need to hire. Here is my example

Month 11/1/2004 12/1/2004 1/1/2005 2/1/2005 3/1/2005 4/1/2005
5/1/2005 6/1/2005 7/1/2005 8/1/2005 9/1/2005 10/1/2005 11/1/2005 12/1/2005
Avg. FTE's Avg. FTE's Avg. FTE's Avg. FTE's Avg. FTE's Avg. FTE's Avg.
FTE's Avg. FTE's Avg. FTE's Avg. FTE's Avg. FTE's Avg. FTE's Avg. FTE's Avg.
FTE's
Data Entry units entered per hour 6.1 6.4 6.9 6.9 7.5 7.5 7.9 8.2 8.5
8.4 8.9 9.1 9.3 9.9


I wanted to use conditional formatting to tell me which months we need to
add a full fte. I would expect excel to shade 3/1/05, 7/1/05, and 12/5/05
I'm not sure how to set this up in excel. THanks

 

[Casey]

Hi,
Let's see if I understand what you are trying to do.
You want the date to change color when a certain threshold is met i
the labor column which is represented in numbers with a single decima
place.
I would place another column in your worksheet with a formula somethin
like this for Row 2 where your numerical data is in column A. Change th
references to fit your sheet.

=IF((A2-TRUNC(A2,0)>=0.9),1,2)
What this formula does is extract the number on the right side of th
decimal point and compare it to 0.9 which seemed to be your breakpoint
and return a 1 if the breakpoint has been equaled or exceeded and a 2 i
it is less than the breakpoint.

From there you can use the result of 1 or 2 as the trigger fo
conditional formatting of your dates

 

[Andre Croteau]

I'm not sure about when you want the colour change, as your colour changes
proposed in Mar, Jul and Dec2005 don't appear to be consistant
I thought you would've had a change in Mar, Jun and Oct

Say your Dec 2004 FTE cell value of 6.4 is located on cell D3, in the
conditional format for that cell, I would have

=int(D3)<>int(C3)

This method would also highlight if there is a DECREASE in FTE required.

HTH

André