Formula to "true" is 3 consecutive conditions are met

[Redsleeper]

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!!!

 

[RobP]

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!

Hi,
sorry, my real day job got in the way yesterday .... but essentially, I thought on your first example that you wanted to only look at up to the month before (so if you have a Sept date in O7, it would look until August only.)

So this part:

<O7-DAY(O7)+1

is essentially generating the date of "1st of current month", and checks that the sequence is "less than that" eg. meaning it happens before that date.

So, if you want to include up to the current day / month - then simply replace it with

<O7

So essenaitlly your formula would look like:

[QUOTE]
=IFERROR(IF(H254="no payments","TRUE",IF(INDEX($T$9:$CX$9,1,(FIND("111",CONCAT(T254:CX254))+2))<$O$7,TRUE,"N/A")),"N/A")
[/QUOTE]

apologies if you solved it already ..

Rob

 

[awoohaw]

@Redsleeper , did you see POST #9.