Formula to "true" is 3 consecutive conditions are met

Redsleeper · Sep 22, 2023

HI, Looking for some assistance with this as I am struggling! – is there a formula that will return “true” if somebody has 3 1s in a row but once/if they have 3 0s it changes back to “N/A” ? it’s to check up to the latest month and not beyond as they will all have 0s in them (this date is in a separate cell as well (O7 in the original spreadsheet) any help appreciated!!!

awoohaw · Sep 22, 2023

Welcome to the forum.

Is your image an expected result?
If these are expected why do you write: "once/if they have 3 0s it changes back to “N/A”" when you have many records with one or two 0's reverting back to no formatting?

If they aren't expected can you give a scenario with the expectations.

As a note, the Mr. Excel forum has a tool, called xl2bb add in (link below) that allows users to post mini worksheets of their work. This does a number of things that will more likely assure you of a speedy solution. 1. It means the forum does not have to recreate your scenario from scratch Which takes time and many forum users will ignore your question. 2. It will have no typos of data errors. 3. The forum can focus on a solution instead of the scenario.

If you cannot use the xl2bb add in, then please post your data in a table (label column and row headers please) provide any formulas you have in the data. And expected results are really helpful.

Thanks in advance.

Redsleeper · Sep 22, 2023

awoohaw said:
Welcome to the forum.

Is your image an expected result?
If these are expected why do you write: "once/if they have 3 0s it changes back to “N/A”" when you have many records with one or two 0's reverting back to no formatting?

If they aren't expected can you give a scenario with the expectations.

As a note, the Mr. Excel forum has a tool, called xl2bb add in (link below) that allows users to post mini worksheets of their work. This does a number of things that will more likely assure you of a speedy solution. 1. It means the forum does not have to recreate your scenario from scratch Which takes time and many forum users will ignore your question. 2. It will have no typos of data errors. 3. The forum can focus on a solution instead of the scenario.

If you cannot use the xl2bb add in, then please post your data in a table (label column and row headers please) provide any formulas you have in the data. And expected results are really helpful.

Thanks in advance.

Thank you.

Yes, its just an image of the expected result - sorry, I probably haven't explained it very well, at any point if one of the rows has a sequence of 3 1s consecutively it should return "true", it will stay true until it then has a run of 3 consecutive 0s where it will revert back to "N/A", so one or two 0s should not change it back it there where 3 consecutive 1s at some point before. Hopefully that's a little clearer?
To give some context its a sheet from a larger workbook that tracks if repayments have been made against loans, if someone has missed a payment it returns a 1, I need to track all that have had at some point 3 missing payments until they have had 3 successful payments

I will try remove some of the data that I cant share and upload a version of the sheet as you say it would quicker and more accurate.

thanks

RobP · Sep 22, 2023

So, heres a shot for you - it looks for the sequence of 0's (or missed payments) that end the month before todays date (which I think you said was stored in O7 ?).

If it finds the sequence, it gives you a TRUE. Main formula is in B2 for example, but I've included another couple in there to show how its made up - you can delete those of course from your version.

Let us know how it looks ?

roster.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

1

May-20

Jun-20

Jul-20

Aug-20

Sep-20

Oct-20

Nov-20

Dec-20

Jan-21

Feb-21

Mar-21

Apr-21

May-21

Jun-21

Jul-21

Aug-21

Sep-21

Oct-21

Nov-21

Dec-21

Jan-22

May-23

Jun-23

Jul-23

Aug-23

Sep-23

Oct-23

Nov-23

Dec-23

Jan-24

2

Person1

TRUE

1

0

1

0

1

0

1

0

1

0

3

4

5

6

7

Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(INDEX(C1:AF1,1,(FIND("000",CONCAT(C2:AF2))+2))<O7-DAY(O7)+1,TRUE,FALSE)
B4	B4	=FIND("000",CONCAT(C2:AF2))
B5	B5	=INDEX(C1:AF1,1,26)
B6	B6	=O7-DAY(O7)+1
O7	O7	=TODAY()

(of course you'll have to adjust the range across the columns to suit your sheet)

awoohaw · Sep 22, 2023

I think this also does what you ask, but it does require two blank columns before your data. What it is doing is taking 3 arrays looking back 2 month and the current month and multiplying if the values are 1 or 0. and then multiplying by the row column number. This will give the last month with 3 consecutive 1's or 3 consecutive 0's. Then comparing those to get TRUE or NA.

Mr excel questions 63.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

1

2

May-20

3

Person 1

#N/A

0

1

0

4

Person 2

#N/A

0

1

0

1

5

Person 3

TRUE

0

1

0

1

0

1

0

1

0

1

0

6

Person 4

TRUE

0

1

0

1

0

7

Person 5

TRUE

0

1

0

1

0

1

0

1

8

Person 6

#N/A

0

1

0

1

0

1

0

1

9

Person 7

#N/A

0

1

0

redsleeper

Cell Formulas
Range	Formula
F2:AR2	F2	=EDATE(E2,1)
B3:B9	B3	=LET( array1,$C3:$AP3,array2,$D3:$AQ3,array3,$E3:$AR3, last3peat1,MAX(IFERROR((array1)(array2)(array3),0)(COLUMN(array3))), last3peat0,MAX(IFERROR((0=array1)(0=array2)(0=array3),0)(COLUMN(array3))), IF(last3peat1 > last3peat0,TRUE,NA()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:AR9	Expression	=E3=1	text	NO

Redsleeper · Sep 26, 2023

awoohaw said:
I think this also does what you ask, but it does require two blank columns before your data. What it is doing is taking 3 arrays looking back 2 month and the current month and multiplying if the values are 1 or 0. and then multiplying by the row column number. This will give the last month with 3 consecutive 1's or 3 consecutive 0's. Then comparing those to get TRUE or NA.

Mr excel questions 63.xlsm
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR
1
2 May-20 Jun-20 Jul-20 Aug-20 Sep-20 Oct-20 Nov-20 Dec-20 Jan-21 Feb-21 Mar-21 Apr-21 May-21 Jun-21 Jul-21 Aug-21 Sep-21 Oct-21 Nov-21 Dec-21 Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23
3 Person 1 #N/A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0
4 Person 2 #N/A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1
5 Person 3 TRUE 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 1 1 1 0 0 1 0 0 0 0 1 1 1 0 0
6 Person 4 TRUE 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 0
7 Person 5 TRUE 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 0 0 1 1 1 1 1 0 1 1 1 1 0 1 1
8 Person 6 #N/A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 1 0 0 0 1 1 1 0 0 0 1
9 Person 7 #N/A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0
redsleeper
Cell Formulas
Range Formula
F2:AR2 F2 =EDATE(E2,1)
B3:B9 B3 =LET( array1,$C3:$AP3,array2,$D3:$AQ3,array3,$E3:$AR3, last3peat1,MAX(IFERROR((array1)*(array2)*(array3),0)*(COLUMN(array3))), last3peat0,MAX(IFERROR((0=array1)*(0=array2)*(0=array3),0)*(COLUMN(array3))), IF(last3peat1 > last3peat0,TRUE,NA()))
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
E3:AR9 Expression =E3=1 text NO

thank you so much for the reply and apologies for the delay in responding I finally got a chance to try this today - this works great for the current month but is there a way to write it so it will update each month automatically? so the arrays will move on to the next months cells?

Redsleeper · Sep 26, 2023

RobP said:
So, heres a shot for you - it looks for the sequence of 0's (or missed payments) that end the month before todays date (which I think you said was stored in O7 ?).

If it finds the sequence, it gives you a TRUE. Main formula is in B2 for example, but I've included another couple in there to show how its made up - you can delete those of course from your version.

Let us know how it looks ?

roster.xlsm
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF
1 May-20 Jun-20 Jul-20 Aug-20 Sep-20 Oct-20 Nov-20 Dec-20 Jan-21 Feb-21 Mar-21 Apr-21 May-21 Jun-21 Jul-21 Aug-21 Sep-21 Oct-21 Nov-21 Dec-21 Jan-22 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 Jan-24
2 Person1 TRUE 1 1 1 1 0 0 1 0 0 1 1 1 1 0 1 1 1 1 1 1 1 1 0 0 0 1 0 0 0 0
3
4 23
5 01/09/2023
6 01/09/2023
7 22/09/2023
Sheet1
Cell Formulas
Range Formula
B2 B2 =IF(INDEX(C1:AF1,1,(FIND("000",CONCAT(C2:AF2))+2))<O7-DAY(O7)+1,TRUE,FALSE)
B4 B4 =FIND("000",CONCAT(C2:AF2))
B5 B5 =INDEX(C1:AF1,1,26)
B6 B6 =O7-DAY(O7)+1
O7 O7 =TODAY()

(of course you'll have to adjust the range across the columns to suit your sheet)

HI, thank you very much for the attempt at this and apologies for the delay in replying - I can almost get this to work how i want with some minor changes - i changed it to this - =IFERROR(IF(H254="no payments","TRUE",IF(INDEX($T$9:$CX$9,1,(FIND("111",CONCAT(T254:CX254))+2))<$O$7-DAY($O$7)+1,TRUE,"N/A")),"N/A") - I was getting a lot of Value results as sometimes the sequence would not be there so added in the iferror, could you please help me to understand which months its looking at? I can see that it looks for the date in O7 in the index array - how can I adjust this part of the formula to look at and include the current month? it seems to not be including it. Sorry if this doe snot make sense! I'm not that great with spreadsheets!

awoohaw · Sep 26, 2023

Redsleeper said:
thank you so much for the reply and apologies for the delay in responding I finally got a chance to try this today - this works great for the current month but is there a way to write it so it will update each month automatically? so the arrays will move on to the next months cells?

I'll see what I can do. I'm assuming you would put the next month in the next available column to the right? Or are you inserting cells at the beginning?
Please confirm.

awoohaw · Sep 26, 2023

Okay, this will give you 100 months.

Mr excel questions 63.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

BZ

CA

CB

CC

CD

CE

CF

CG

CH

CI

CJ

CK

CL

CM

CN

CO

CP

CQ

CR

CS

CT

CU

CV

CW

CX

CY

CZ

1

2

May-20

3

Person 1

#N/A

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

4

Person 2

#N/A

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

5

Person 3

TRUE

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

6

Person 4

TRUE

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

7

Person 5

TRUE

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

8

Person 6

#N/A

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

9

Person 7

TRUE

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

redsleeper

Cell Formulas
Range	Formula
F2:CZ2	F2	=EDATE(E2,1)
B3:B9	B3	=LET( array1,$C3:$CX3,array2,$D3:$CY3,array3,$E3:$CZ3, last3peat1,MAX(IFERROR((array1)(array2)(array3),0)(COLUMN(array3)-4)), last3peat0,MAX(IFERROR((0=array1)(0=array2)(0=array3),0)(COLUMN(array3))), RESULT,IF(last3peat1 > last3peat0,TRUE,NA()),RESULT)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:CZ9	Expression	=E3=1	text	NO

Redsleeper · Sep 26, 2023

awoohaw said:
I'll see what I can do. I'm assuming you would put the next month in the next available column to the right? Or are you inserting cells at the beginning?
Please confirm.

thank you so much, yes the nexts month data will be in the next column to the right.

Formula to "true" is 3 consecutive conditions are met

Redsleeper

New Member

Excel Facts

awoohaw

Well-known Member

Redsleeper

New Member

RobP

Well-known Member

awoohaw

Well-known Member

Redsleeper

New Member

Redsleeper

New Member

awoohaw

Well-known Member

awoohaw

Well-known Member

Redsleeper

New Member

Similar threads

Forum statistics

Share this page

Formula to "true" is 3 consecutive conditions are met

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock