multiply IF arguments for RAG

[have-a-go-green]

Please can someone, tell me what is wrong with my formula.


my aim, is to add a RED, AMBER, GREEN, column to a range of advisor stats.


=If(C2<G$1,"Target","",IF(C2<F$1,"Stretch","",IF


(C2>G$1,"Loser",))

 

[NARAYANK991]

Hi ,


The following IF statement is a valid statement , but I am not sure it will do what you want to do :


=IF(C2<G$1,"Target",IF(C2<F$1,"Stretch",IF(C2>G$1,"Loser","")))


An IF statement has 3 parameters :


1. The condition to be tested

2. The action to be taken if the tested condition is TRUE

3. The action to be taken if the tested condition is FALSE


Each of these parameters is separated by a "," ( comma ).


In the formula you have posted , the first IF has 4 parameters ; it also does not have a closing parenthesis.


But to get back to the intent of the IF statement :


The first IF statement checks if C2 is less than G1 ; if so , the text "Target" is placed in the cell ; if not , C2 can be equal to G1 , or it can be greater than G1. The second IF tests for whether C2 is less than F1 ; if yes , the text "Stretch" is placed in the cell ; if not , the next IF statement checks for whether C2 is greater than G1.


Two points are to be noted :


1. What will happen if C2 is equal to or greater than F1


2. What will happen if C2 is equal to G1


Are these two situations relevant to your worksheet ?


Narayan

 

[have-a-go-green]

Hi Narayan


You desrve a medal for the amount of effort you put in to your responses


I do have to apologise though, as i have a very bad habbit, of hitting a brick wall, with a problem and posting my query on here, but, then end up resolving it myself.


=IF(C2<=F$1,"Stretch",IF(C2<=G$1,"target",IF(C2>=G$1,"Loser")))


Your Response with spot on. one thing i am unsure of though, is, why this task could not be accomplished by conditional formatting alone, based on the figures (stats) in the cells. I did try this Last night, but it kept going wrong, but i cant see why. Ive appied conditional formatting to the "target,Stretch, and loser" column, which, works fine with the formula. surely this can be done with just conditional formatting?


60 target

23 Stretch

60 target

23 Stretch

78 Loser

43 Stretch

23 Stretch

62 Loser

59 target

45 Stretch

67 Loser

 

[NARAYANK991]

Hi ,


Thanks for your appreciative comment.


I am not sure about how your data is organized ; what I did was , copy your data into column A.


In F1 , I put in the value 50 ; in G1 the value 60.


Now , in column B , I used the formula =$G$1-A1 ; in column C , I used the formula :


=IF(B1<0,B1,IF(B1<$G$1-$F$1,0,$F$1-B1))


This column , I formatted using the following custom format :


"Stretch";"Loser";"Target"


Does this give what you want ?


Narayan