Consective Values after specfic value formula

[shaktimair]

Hello all

This is my first post on this forum and needed some help on what I am trying to do.

I have data that stretches across a few cells and shows the grade the advisor received in date order and the formula I am trying to build is to check they competency rating and I want the formula to look at each row and soon as it finds 'Red Agree' in a cell to mark as 'Action Required' but if after that 'Red Agree' grade they get 3 consecutive 'Green Agree' then make it 'No Action Required'

I hope this is enough information, however please advise if any more details required.

I have a project at work and really need someone's help, many thanks in advance for your time.

Regards

Shakti Mair


Example below:
Top one - As 3 greens after the red = No Action Required
Bottom one - As 3 greens after red = Action Required

[TABLE="width: 998"]
<tbody>[TR]
[TD]Check Grade 1
[/TD]
[TD]Check Grade 2
[/TD]
[TD]Check Grade 3
[/TD]
[TD]Check Grade 4
[/TD]
[TD]Check Grade 5
[/TD]
[TD]Outcome
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/11/2017
[/TD]
[TD]11/11/2017
[/TD]
[TD]12/11/2017
[/TD]
[TD]13/11/2017
[/TD]
[TD]14/11/2017
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red Agree
[/TD]
[TD]Green Agree
[/TD]
[TD]Green Agree
[/TD]
[TD]Green Agree
[/TD]
[TD]Green Agree
[/TD]
[TD="colspan: 2"]No Action Required
[/TD]
[/TR]
[TR]
[TD]Green Agree
[/TD]
[TD]Green Agree
[/TD]
[TD]Red Agree
[/TD]
[TD]Green Agree
[/TD]
[TD]Green Agree
[/TD]
[TD="colspan: 2"]Action Required
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AFPathfinder]

So, it really just needs to look at the three cells to the left and find "Red Agree", then, right?

=IF(ISNA(MATCH("Red Agree",C3:E3,0)),"No Action Required","Action Required")

 

[shaktimair]

Firstly thank you for taking time out to help me. The formula suggested does not quite work the way I need it to.

If a cell is ‘Red Agree’ in a range then ‘Action Required’ but if after the ‘Red Agree’ there is 3 consecutive ‘Greens’ in the same range, then ‘No Action Required’.

Example:
Green Red Green Green Green - No Action Required

Green Red Green Green - Action Required

I hope this make sense and really appreciate if you can help with the formula.

Many thanks

Shakti

 

[AFPathfinder]

In my testing, I believe the formula does just that. The columns are chronological from left to right, correct? And, any "Red Agree" within the last three grades would equal an "Action Required" Outcome? If there are no "Red Agree" values within the last three grades, then the advisor either had no "Red Agree" or has had at least three "Green Agree" values since their last Red Agree.

If my assumptions above are correct, the only issues I can see with this solution are:
1. If the "Red" and "Green" values are actual colors of the cell interiors or font. I don't believe there is a cell function that can compare colors (unless it's a UDF).

2. If a new column is inserted within the formula range, the formula range will expand instead of shift to the right. The formula will have to be adjusted.

3. If the formula is being setup to the far right with blank columns in between and the formula needs to discern the location of the last three grades in a larger range, the formula would have to be much more elaborate or move to a VBA solution.

 

[shaktimair]

Hi, thanks for your reply. I have re-tested the formula and unfortunately it’s not working.

The formula works if all I want to do is, if any Red Agree in a row, then Action Required.

But I need it to work slightly different, as if there are 3 Greens after the Red, then No Action Required needs to be displayed, but this does not happen with the formula.

I can send you my testing via email so you can see what I mean.

Kind Regards

Shakti