Conditional Formatting/Formula Problem
- From: Evandavies75@xxxxxxxxx
- Date: Thu, 17 Jul 2008 10:57:40 -0700 (PDT)
I am having a problem with conditional formatting. It involves a
monthly report I generate to track time that's been used to-date
against annual fees a client pays me for several projects. There are
two key columns on this report; one shows the percentage of hours
"purchased" for the year that have been used year-to-date, the other
shows the actual number of hours available for the remainder of the
year. So if the client paid me an annual fee that buys 1,000 hours
and in April they have used 80% of those hours "80%" shows in the
first column and "200" appears in the adjoining cell.
I use conditional formatting to make it easier for the client to
eyeball all the projects listed in order to quickly see which have a
good cushion of hours remaining, the ones that deserve attention and
the ones where they've exceeded the fee. To accomplish this the
percentage cells have three conditions: "cell value to less than or
equal to .85" is shaded green to show there is sufficient cushion;
"cell value is greater than or equal to 1" is shaded red to show the
fee is exceeded; and "cell value is between .85 and 1" is shaded
yellow to show that attention needs to be paid to this project because
the hours are getting close to being used up.
This works like a charm. The problem is occurring in the adjoining
column where the remaining hours are shown. I have three formula
conditions tied to the value in the "percentage" cells. So if the
value is less than or equal 85% the hours cell is shaded green, or if
the value is greater than 1 the hours cell is shaded red. The problem
boils down to the "yellow" condition.
The formula I'm using is =(J10>0.85)*(J10<1). I thought this formula
had worked for me in the past, but maybe I was dreaming it. In any
case, can someone tell me if there is a problem with this formula, or
should I be using an entirely different one?
.
- Follow-Ups:
- Re: Conditional Formatting/Formula Problem
- From: Bob Greenblatt
- Re: Conditional Formatting/Formula Problem
- Prev by Date: Re: Columns into Rows
- Next by Date: cant paste charts anymore??
- Previous by thread: Re: Need a date formula to divide a date into 3 columns
- Next by thread: Re: Conditional Formatting/Formula Problem
- Index(es):
Relevant Pages
|
