# Consective Values after specfic value formula

#### shaktimair

##### New Member
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

 Check Grade 1 Check Grade 2 Check Grade 3 Check Grade 4 Check Grade 5 Outcome 10/11/2017 11/11/2017 12/11/2017 13/11/2017 14/11/2017 Red Agree Green Agree Green Agree Green Agree Green Agree No Action Required Green Agree Green Agree Red Agree Green Agree Green Agree Action Required

<tbody>
</tbody>

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### AFPathfinder

##### Well-known Member
So, it really just needs to look at the three cells to the left and find "Red Agree", then, right?
Code:
``=IF(ISNA(MATCH("Red Agree",C3:E3,0)),"No Action Required","Action Required")``

#### shaktimair

##### New Member
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

##### Well-known Member
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

##### New Member
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

Replies
3
Views
202
Replies
1
Views
3K
Replies
8
Views
1K
Replies
3
Views
235
Replies
7
Views
426

1,127,028
Messages
5,622,291
Members
415,890
Latest member
macak333

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back