Traffic Lights

[Walshy]

Please could someone help me...

I am trying to get a traffic light effect going on with
some statistics im producing... The criterea being :

if the amount is under target its green
if the amount is over target red
(Tricky part) If the amount is under target but rising for
2 months or more then its amber ???

Stats go something like this :

Target 100
January 50
February 60
March 70

=Amber...

Could someone please help me ? I just want the formula to
say either green/amber/red for the result

Thanks

 

[Ken Wright]

You don't cover all eventualities, ie =Target, what about Months 1 and 2 where
you don't have enough history for it have been able to rise by 2 months etc.
That having been said:-

A B C
1 Target 100
2 Jan 50
3 Feb 60
4 Mar 70 Amber
5 Apr 65 Green
6 May 70 Green
7 Jun 68 Green
8 Jul 80 Green
9 Aug 90 Amber
10 Sep 100 Other
11 Oct 110 Red
12 Nov 105 Red
13 Dec 110 Red

Formulas in:-

C4
=IF(B4>$B$1,"Red",IF(AND(B4>B3,B3>B2,B4<$B$1),"Amber",IF(B4<$B$1,"Green","Other"
)))
C5
=IF(B5>$B$1,"Red",IF(AND(B5>B4,B4>B3,B5<$B$1),"Amber",IF(B5<$B$1,"Green","Other"
)))
C6
=IF(B6>$B$1,"Red",IF(AND(B6>B5,B5>B4,B6<$B$1),"Amber",IF(B6<$B$1,"Green","Other"
)))
C7
=IF(B7>$B$1,"Red",IF(AND(B7>B6,B6>B5,B7<$B$1),"Amber",IF(B7<$B$1,"Green","Other"
)))
etc

but just put the one in C4 and then copy down

This can also be done with conditional formatting to actually colour the cells
these colours automatically.

 

[JE McGimpsey]

One way, using conditional formatting:

You don't say what should happen if the amount is equal to the target.
I'll assume it is the same as under target.

Further, I'll assume your data below is in A1:B4. Format B4 as Green.
Then the CF for B4 could be:

CF1: =B4>B$1
Format1: Red

CF2: =AND(B4>B3,B3>B2)
Format2: Amber