conditional formatting?
 

How do I apply a conditional format or set of formats to highlight revenues
in a table when they drop for at least three consecutive months in a row. I
do not want any cells to be highlighted unless they are down 3 or more months
in a row and the way I'd had it setup was the cell was yellow first month
drop, second orange and third month down was red, but cells that we down even
1 month would be changed to yellow etc. I would like to set this up where it
I wouldn't have to do anything except update from the main data sheet and all
the conditional formats would be in place. It's not working the way I'd
setup.

a sample of what it looks like is below and it just continues on through the
months like this: in this case the 1-3 cells would need to be highlighted but
drops off and Aprils would not as numbers are back up.

Jan Feb March April
$39,853.00 $38,644.00 $32,374.00 $43,469.00

Here's an idea to make this work: insert a row above or below your
revenue line (wherever it makes sense) and for each month in the new
row write a formula: IF(current month revenue is less than last month's
revenue,1,0). Then have your conditional formatting cell add up the 1
or 0 value in the new row for the current month plus the last two
months. Set the formatting to paint the cell yellow if that sum is 1,
orange if 2, red if 3. For aesthetic purposes, you can hide the newly
inserted row, or format the font to show up white on a white background
(not visible). Show it to your boss, so he/she will think you're a
genius, then leave early because it's Friday afternoon. Whoo hoo!

Dave thanks for the tip! Is there a way since there are over 800 clients
with stats in this report to make your suggestion work for the entire report
by adding blank line at the top of the table, otherwise if I understand you,
I will need to add a blank line and apply the conditional formatting you
suggested above each of the 800 rows?

Thanks for your help, I'll watch for your reply, so I can leave early today,
great idea on your part.

"Dave O" wrote:

Here's an idea to make this work: insert a row above or below your
revenue line (wherever it makes sense) and for each month in the new
row write a formula: IF(current month revenue is less than last month's
revenue,1,0). Then have your conditional formatting cell add up the 1
or 0 value in the new row for the current month plus the last two
months. Set the formatting to paint the cell yellow if that sum is 1,
orange if 2, red if 3. For aesthetic purposes, you can hide the newly
inserted row, or format the font to show up white on a white background
(not visible). Show it to your boss, so he/she will think you're a
genius, then leave early because it's Friday afternoon. Whoo hoo!

If you *need* to provide this conditional formatting by client, then
yes, this would need a new row for each client. That would get ugly in
a hurry.

If you need to show changes in the total revenue for all clients, then
you can perform this process on just the total row.

Give me a few minutes to think about this one- I'm on a conference
call, and just got an action item.

OK, Deb, sorry for the delay.

New idea- you *don't* need a separate row for each conditional format.
Here's how to do it: assuming month 1 revenue starts in cell F3. Open
the conditional formatting window and set condition 1 to "Formula is".
In the formula box immediately to the right of that, enter
=AND(F3<E3,F3<D3,F3<C3)
Set the format to paint that cell red.

Click the Add button, set condition 2 to "Formula is", and enter this
formula:
=AND(F3<E3,F3<D3)
Set the format to paint that cell orange.

Click the Add button, set condition 3 to "Formula is", and enter this
formula:
=AND(F3<E3)
Set the format to paint that cell yellow.

Then follow the show your boss/go home early instructions.

Sorry for the delayed response!

If you *need* to provide this conditional formatting by client, then
yes, this would need a new row for each client. That would get ugly in
a hurry.

If you need to show changes in the total revenue for all clients, then
you can perform this process on just the total row.

Give me a few minutes to think about this one- I'm on a conference
call, and just got an action item.

Dave,

This works great! What can I do to make sure the only cells highlighted
have been down consecutively for at least 3 months. The colors worked great,
but since we have clients that drop for 1 month or even 2 months at a time,
the boss only wants to see cells highlighted that have bottomed out for 3
consecutive months or longer. What code needs to be added to remove single
and double months highlights.

Thanks so much for your assistance, this is making it much easier for me.

"Dave O" wrote:

OK, Deb, sorry for the delay.

New idea- you *don't* need a separate row for each conditional format.
Here's how to do it: assuming month 1 revenue starts in cell F3. Open
the conditional formatting window and set condition 1 to "Formula is".
In the formula box immediately to the right of that, enter
=AND(F3<E3,F3<D3,F3<C3)
Set the format to paint that cell red.

Click the Add button, set condition 2 to "Formula is", and enter this
formula:
=AND(F3<E3,F3<D3)
Set the format to paint that cell orange.

Click the Add button, set condition 3 to "Formula is", and enter this
formula:
=AND(F3<E3)
Set the format to paint that cell yellow.

Then follow the show your boss/go home early instructions.

Sorry for the delayed response!

No worries, glad to help. I mis-read your original message, and
thought you still wanted the yellow-orange-red scenario. To get a
three month scenario only, remove conditions 2 and 3 from the solution
I sent earlier.

Getting late, here on the East coast- that "leave early" idea is gonna
be tough to implement.