Nested IF function???

[worzell]

Hello there,

I am hoping someone can help!

I am producing a spreadsheet that will allocate a Red / Amber / Green
status based on target hit.

The guideline is

Green - on target
Amber - within 95% of target (either above or below target)
Red - more than or less than 95% of target

ie where the target is 82%

A1 B2 C2
Actual Target Status

IF the actual is 82% then the status is Green
IF the actual is greater than or equal to 77.9% (95% of target) it is
Amber
IF the actual is less than or equal to 86.3% (95% of target) it is
Amber
IF the actual is less than 77.9% (95% of target) it is Red
IF the actual is greater than 86.3% (95% of target) it is Red

Or written with references:

A1 B2 C2
Actual 82% Formula needed!!!

IF A1 = B2 then C2 = "Green"
IF A1 >= B2 * 95% then C2 = "Amber"
IF A1 <= B2 / 95% then C2 = "Amber"
IF A1 < B2 * 95% then C2 = "Red"
IF A1 > B2 / 95% then C2 = "Red"

Many thanks in advance to anyone who can help!

Cheers,

Worzell

 

[JE McGimpsey]

One way:

C2: =IF(A2=B2,"Green",IF(ABS(1-B2/A2)<=0.05,"Amber","Red"))

 

[worzell]

Thats great - thank you!!