Formula Help

Solved/Closed
Report
-
 Shaun -
I'm trying to create a formula to look at the values in a complete row and if 3 of those values in a row are pass then it will fill in the cell with Yes.

A B C D E F G H I
PASS PASS FAIL PASS PASS PASS

For instance in the above example this row has indicating whether they pass or fail a work area and column I will indicate whether they are certified or not based on if they have 3 passes in a row. A would be their first work area, B would be the second and so on, so I also need for the formula in column I to keep looking at A-H as they progress so I wouldn't show a Yes until it is looking at cells D-F having 3 passes in a row, but if they were to Fail in column G then the Yes would either disappear or change to No. Is there any way to create a formula like this?
Thanks

8 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Based on you clarification

IF(MAX(IF(D1&E1&F1&G1&H1<>"",0,1) * COUNTIF(A1:C1,"PASS"),IF(E1&F1&G1&H1<>"",0,1) * COUNTIF(B1:D1,"PASS"),IF(F1&G1&H1<>"",0,1) * COUNTIF(C1:E1,"PASS"),IF(G1&H1<>"",0,1) * COUNTIF(D1:F1,"PASS"),IF(H1<>"",0,1) * COUNTIF(E1:G1,"PASS"),COUNTIF(F1:H1,"PASS"))=3,"PASS","FAIL?")


This formula looks for data in row 1 columns A-H. It looks for last three block that are filled. If that block has thRee PASS, then it display pass ELSE IT WILL SHOW FAIL?
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Thank you very much, that worked great. A couple small things I still need to try to see about fine tuning but it's a lot farther than I would have been and the fine tuning isn't entirely necessary. Thanks again for all your help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this


=IF(MAX(COUNTIF(A1:C1,"PASS"),COUNTIF(B1:D1,"PASS"),COUNTIF(C1:E1,"PASS"),COUNTIF(D1:F1,"PASS"),COUNTIF(E1:G1,"PASS"),COUNTIF(F1:H1,"PASS"))=3,"PASS","FAIL?")


This formula looks for data in row 1 columns A-H. If there is a block of three cells that show "PASS" then formula will show pass. If there are no such three cell block, formula will show "FAIL?".
Hi Shaun,

I didn't fully understand your detailed explanation, but if you want to display a Yes when Pass appears a least 3 times use these formula's:

In cell J1: =SUM(IF(A1:H1="PASS",1,0)) confirm formula by hitting Ctrl+Shift+Enter.
In cell I1: =IF(J1>=3,"Yes","No")

The first formula is an array formula and will count how many times the value Pass is present in the range A1:H1. You can either hide the column or change the textcolor to white if you do not wish to see the result.
The second formula looks at the number and either puts a Yes or a No in cell I1.

Best regards,
Trowa
Thanks, it's close but in the first formula provided I don't want it to look at any block of 3 in the row to give a yes if there are 3 passes in a row.

In the second formula again it is calculating every pass in that row.

What I would like it to do is always look at the last 3 cells in that row that have entries in them and see if those last 3 have 3 consecutive passes and if they do then a yes is put in at the end, but if the next cell ends up being a Fail then the end cell will automatically switch to NO, then in order for them to get it back to Yes then the next 3 cells would need to be filled in with Pass before it changes back to Yes.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
So are you saying is that in A-H, you are only interested in last 3 filled cells and that will determine if there is a yes or a no ?
Correct, but i want to be able to do so without needing to revise the formula each time to specifically look at the last 3 and be able to keep the yes or no to populate in that same end cell.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok clarify one more thing. Will there be an empty cell in between. ? Like
A1, B1, C1, E1 are filled but D1 is not filled ?
No, should not have any empty cells in between. A1, B1, C1, D1, and E1 should all have either Pass or Fail in them, F1 could have another random value like "QQ" in it and all following cells would be empty. If the "QQ" can be ignored that would be great, it can be left blank if that is not possible.