Formula for return a "1" if certain numbers are found

[cl604]

Hi guys

I am using Excel 2011.

I am trying to make a formula that will return a 1 if certain numbers like 26, 52, 35 etc up to 20 different ones are found in Column Ac1 down to Ac500. If no numbers are found then it just says 0.

I tried this formula but it does not do multiples IF(ISNA(VLOOKUP(26,$AC$5:$AC$15,0,FALSE)),"1","0")
I am thinking now that this formula is not the way to go.

thoughts?

 

[mikerickson]

"the number 26 might appear at 78" - what does that mean?

 

[cl604]

26 might appear at cell 78,199 might appear at cell 50 and 10 might come at cell 5. I am racking my brain to get a formula to return a 1 if that exact sequence occurs. Ie 26 then 199 then 50 in that order.So 26 must appear lowest on the cell count.

Thanks for your help. appreciate it.

 

[Rick Rothstein]

Cheers thanks for that. The only problem is the sequences don't appear one after another. IE the number 26 might appear at a 78, 199 might appear at 50 and 10 might come at 5. Once that sequence appears a 1 must be produced.Just like this =SIGN(SUMPRODUCT(COUNTIF(AC1:AC500, {26, 199,10}))). Except the sequence must be exact.

I'm sure once Mike sees what you want, he will come up with a more efficient formula than this one, but it is all I could think of...

=IFERROR(1*AND(MATCH(26,AC1:AC500,0)< MATCH(199,AC1:AC500,0),MATCH(199,AC1:AC500,0)< MATCH(10,AC1:AC500,0),MATCH(26,AC1:AC500,0)< MATCH(10,AC1:AC500,0)),0)

 

[Rick Rothstein]

I'm sure once Mike sees what you want, he will come up with a more efficient formula than this one, but it is all I could think of...

=IFERROR(1*AND(MATCH(26,AC1:AC500,0)< MATCH(199,AC1:AC500,0),MATCH(199,AC1:AC500,0)< MATCH(10,AC1:AC500,0),MATCH(26,AC1:AC500,0)< MATCH(10,AC1:AC500,0)),0)

By the way, in case you would be interested, here is a UDF (user define function) that you could use if you wanted...

Function Sequence_26_199_10(R As Range) As Long
  Sequence_26_199_10 = -("/" & Join(Application.Transpose(R), "/") & "/" Like "*/26/*/199/*/10/*")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Sequence_26_199_10 just like it was a built-in Excel function. For example,


=Sequence_26_199_10(AC1:AC500)


If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[cl604]

Great i will check UDFs out. I am having a look at the formula you made to see if i can use it. cheers

 

[mikerickson]

I'm sure once Mike sees what you want, he will come up with a more efficient formula than this one, but it is all I could think of...

=IFERROR(1*AND(MATCH(26,AC1:AC500,0)< MATCH(199,AC1:AC500,0),MATCH(199,AC1:AC500,0)< MATCH(10,AC1:AC500,0),MATCH(26,AC1:AC500,0)< MATCH(10,AC1:AC500,0)),0)

Actualy, the only improvement that I could see is that the last inequality isn't needed


To the OP,
Would the sequence

26, 55, 10, 55, 199, 55, 10 count?

The sub-sequence 26, 199, 10 is present.

 

[cl604]

Yes the sequence would count as long as 26 is the lowest of the cells so less than 199 which comes next then 10. I should have said before that the data is constantly feed into cell ac1 and moves down a cell every 1 minute as new data is produced. A formula works in the background to give a number into ac1. I want to recognise certain sequences like the 26,199,10.
Cheers guys.

 

[Rick Rothstein]

Actualy, the only improvement that I could see is that the last inequality isn't needed

If you leave it out, then you could get a false match with a sequence like this...

44 199 55 10 66 26 77 199 88

 

[mikerickson]

It appears that you are looking to see if (from top to bottom) there is a 10 above a 199 which is above a 26

Define a Name
Name: BelowFirst10
RefersT0: =INDEX(A1:A100, MATCH(10,A1:A100,0), 1): A100

Name: Below199Below10
RefersTo: = INDEX(Below10, MATCH(199, Below10, 0), 1):A100

Name: Below26Below199Below10
RefersTo: =INDEX(Below199Below10, MATCH(26, Below199Below10, 0),1), 1):A100

The formula =ISNUMBER(COUNTA(Below26Below199Below10) will return TRUE if the condition is met.

Rick's UDF will do the same.

 

[mikerickson]

If you leave it out, then you could get a false match with a sequence like this...

44 199 55 10 66 26 77 199 88

From the OPs response to my question about duplicates, I think that 199, 10, 26, 199 would meet the condition.
The formula would also give an improper FALSE to my sequence in #16.

It seems to me that the OP's question could be restated "Given a sequence of numbers, is 26, 199, 10 a sub-sequence?" (Add in the OP's preference for a sequence that goes upward)
That is the question that Rick's UDF answers.