# Formula Help

Solved/Closed
Related:

- Formula Help
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Date formula in excel dd/mm/yyyy - Guide
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Credit summation formula - Guide

## 8 responses

rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766

Apr 8, 2010 at 11:48 AM

Apr 8, 2010 at 11:48 AM

Based on you clarification

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?

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?

rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766

Apr 8, 2010 at 08:53 AM

Apr 8, 2010 at 08:53 AM

Try this

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?".

=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

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

The second formula looks at the number and either puts a

Best regards,

Trowa

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.

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.

Didn't find the answer you are looking for?

Ask a question
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766

Apr 8, 2010 at 11:19 AM

Apr 8, 2010 at 11:19 AM

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.

rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766

Apr 8, 2010 at 11:41 AM

Apr 8, 2010 at 11:41 AM

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 ?

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.

Apr 8, 2010 at 02:14 PM