Home |
Search |
Today's Posts |
#1
|
|||
|
|||
patterns
Alright, I have another. The problem this time is I want excel to recognize a pattern. If I have a list of 8 numbers, I want to know if there is ever 5 consecutive numbers. 2 5 4 10 8 6 3 4 I want it to recognize that there is a string of 5 (2,3,4,5,6) -- DArinello ------------------------------------------------------------------------ DArinello's Profile: http://www.excelforum.com/member.php...o&userid=25589 View this thread: http://www.excelforum.com/showthread...hreadid=390090 |
#2
|
|||
|
|||
If your numbers are one to a cell, in column A, you could drop this formula
in B5 and autofill through column B: =IF(AND(A5=A4+1,A5=A3+2,A5=A2+3,A5=A1+4),"Run","") "DArinello" wrote: Alright, I have another. The problem this time is I want excel to recognize a pattern. If I have a list of 8 numbers, I want to know if there is ever 5 consecutive numbers. 2 5 4 10 8 6 3 4 I want it to recognize that there is a string of 5 (2,3,4,5,6) -- DArinello ------------------------------------------------------------------------ DArinello's Profile: http://www.excelforum.com/member.php...o&userid=25589 View this thread: http://www.excelforum.com/showthread...hreadid=390090 |
#3
|
|||
|
|||
Just to keep the ball rolling a little further here .. think this option
(experimental) might bring us a little closer to one workable solution for the pattern detection. It seems to return ok maybe 70%+ of the time <g Assuming the source list of numbers is in A1:A8 (w/o any blank cells). And as per sample data in the orig. post, it was assumed that the source list of numbers would be in random sequence and could contain duplicates Placed in: B1: =IF(COUNTIF($A$1:A1,A1)1,"",ROW()) C1: =IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(B:B,ROWS($A$1 :A1)),B:B,0))) D1: =IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",SMALL(C:C ,ROWS($A$1:A1))) E1: =IF(ISERROR(ABS(D1-D2)),"",ABS(D1-D2)) Selected B1:E1, filled down to E8 Set-up the monitoring / result cells: Placed in say, F1, and array-entered the formula, i.e. pressed CTRL+SHIFT+ENTER: =IF(MATCH(1,--($E$1:$E$8<1),0)=1,"",SUM(E1:INDIRECT("E"&MATCH(1 ,--($E$1:$E$ 8<1),0)-1))) Placed in F2, and array-entered the formula: =SUM(INDIRECT("E"&MATCH(1,--($E$1:$E$8<1),0)+1):E8) Placed in F3: =IF(MAX(F1:F2)+1=5,"Exactly 5 consecutive numbers",IF(MAX(F1:F2)+15,MAX(F1:F2)+1&" consecutive numbers","")) Kept an eye on the result cells F3, and tested with various sample data in A1:A8 If there's exactly 5 consecutive numbers within A1:A8, we should get the message to the effect in F3, viz.: "Exactly 5 consecutive numbers" And if there's more than 5 consecutive numbers within A1:A8, we should get one of the following 3 messages in F3, depending on the evaluated result: "6 consecutive numbers" "7 consecutive numbers" "8 consecutive numbers" If there's less than 5 consecutive numbers within A1:A8, F3 should remain blank Sample testing reveals the above seems to return correctly perhaps 70%+ of the time in F3. It returns incorrectly with say, these sort of data in A1:A8 1 2 4 5 6 8 2 2 which reduces to an ascending sorted range of uniques (in col D): 1 2 4 5 6 8 which is 2 sets of consecutives: 1,2 and 4,5,6 both less than 5 consec's. The incorrect return triggered in F3 is: "Exactly 5 consecutive numbers" when F3 should just remain blank. Hopeful that perrhaps others would step-in here and offer their insights / alternatives / improvements to bring us further down the road ? -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
Placed in F3:
=IF(MAX(F1:F2)+1=5,"Exactly 5 consecutive numbers",IF(MAX(F1:F2)+15,MAX(F1:F2)+1&" consecutive numbers","")) Sorry, pasted the wrong formula above for F3 In F3 should be: =IF(MAX(F1:F2)+1=5,"Exactly 5 consecutive numbers",IF(MAX(F1:F2)+15,MIN(MAX(F1:F2)+1,8)&" consecutive numbers","")) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
E1:
=IF(ISERROR(ABS(D1-D2)),"",ABS(D1-D2)) Oops, in E1 should be: =IF(ROWS($A$1:A1)=8,"",IF(ISERROR(ABS(D1-D2)),"",ABS(D1-D2))) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I do conditional formatting on number formats not patterns. | Excel Discussion (Misc queries) | |||
can I customize cell fill patterns in Excel? | Excel Discussion (Misc queries) | |||
Printing Patterns in Cells | Excel Discussion (Misc queries) | |||
Excel cell patterns | Excel Discussion (Misc queries) | |||
Copying spreadsheet with fonts and patterns | Excel Discussion (Misc queries) |