RAG Status help!!

[Rich8632]

Hi,
Is there a nested if formula that can result in a RAG status for the following Criteria?

R if less than 1.5 or more than 4
A if between 1.5 and 2 or between 3.5 and 4
G if between 2.1 and 3

Thanks

 

[Joe4]

Welcome to the Board!

How about something like (for a value in A1):

=IF(OR(A1<1.5,A1>4),"R",IF(OR(AND(A1>=1.5,A1<=2),AND(A1>=3.5,A1<=4)),"A",IF(AND(A1>=2.1,A1<=3),"G","None")))

If it falls into one of the gaps that you haven't accounted for (like between 3 and 3.5), it will return "None".

 

[mikerickson]

Try
=LOOKUP(A7,{-9E+99,1.5,2.0001,2.1,3.0001,3.5,4.0001,9E+99},{"R","A",33,"G",55,"A","R",88})


The .0001 bits are to account for the equals conditions, use a smaller number if that is to "grainy" for your data.

 

[Rich8632]

Welcome to the Board!

How about something like (for a value in A1):

=IF(OR(A1<1.5,A1>4),"R",IF(OR(AND(A1>=1.5,A1<=2),AND(A1>=3.5,A1<=4)),"A",IF(AND(A1>=2.1,A1<=3),"G","None")))

If it falls into one of the gaps that you haven't accounted for (like between 3 and 3.5), it will return "None".

That's great, I meant to include up to 3.5 in the G status so that should do it!


Thanks for your help

 

[Joe4]

You are welcome.