Needing help to analyse number sequences, please

[cockneyjim]

Hi and best wishes.

I'm returning to Excel
after many years.

It's amazing how much one forgets!

I'm hoping for some advice on constructing
a method to sort out some numerical results.

Basically, I need to take readings from a machine
in a laboratory. Each reading will yield 5 numbers.

Each reading will have a whole number value of between 1 and 99,
which I will enter into columns ABCD and E.

I need to read each row of 5 numbers and somehow display or highlight
all the rows that have 3,4 and 5 consecutive numbers.

I also need to display or highlight those rows where 3,4 and 5 numbers are all even or all odd.

Any ideas would be welcomed, or even point me to a tutorial that might
deal with such a problem.

Many thanks in advance.

Best wishes

Jim

 

[tomwhi]

Hi Jim,

Could you provide an example?

Thank you
Tom

 

[Storax]

Maybe this UDF is helpful (Attention: no ErrorHandling etc.!)

Function How_Many_Are_Odd(rg As Range) As Byte

Dim i As Integer
Dim intArr() As Integer
Dim sngCell As Range

   
   ReDim intArr(rg.Count)
   i = 1
   For Each sngCell In rg
      intArr(i) = sngCell.Value
      i = i + 1
   Next


   How_Many_Are_Odd = 0
   
   For i = 1 To UBound(intArr)
      If intArr(i) = 1 Then
         How_Many_Are_Odd = How_Many_Are_Odd + 1
      Else
         If (intArr(i) Mod 2) <> 0 Then
            How_Many_Are_Odd = How_Many_Are_Odd + 1
         End If
      End If
   Next

End Function

The function How_Many_Are_Even would be similar

 

[cockneyjim]

Hi Tom,
Basically, I would take a reading of five numbers from a machine, say every hour.
A reading might be something like: 2 , 7 , 56 , 10 , 83
Each time I take a reading I enter the numbers under columns ABCD and E in Excel.
The next reading goes in the next row, and so on.
So a few hours worth of readings would look something like:

A B C D E
1 33 8 77 21 9
2 5 41 1 67 12
3 18 2 32 6 94
4 7 19 3 55 21
5 16 5 12 43 7
and so on.
Basically I want to identify which rows have 3 or more sequential numbers, 1,2,3 etc.
I also want to identify which rows have all even numbers or all odd numbers.
These can be two seperate processes if necessary.
The method of identification doesn't matter.
It can be a change in the colour of the rows or
the placing of the identified rows on the next page or
any other way that works easily.
That's basically the problem!
Many thanks for taking the time.
-------------------------------------------------------------------------------------------------------
Hi Storax,
Many thanks for your UDF.
I'll give it a try!
Looking at it, I'm realising just how much I've forgotten about Excel!!
Many thanks for taking the time and effort.
Sincerely appreciated!

 

[shg]

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Num1​	Num2​	Num3​	Num4​	Num5​	Seq​	Odd​	Even​
2​	33​	8​	77​	21​	9​	1​	4​	1​
3​	5​	41​	1​	67​	12​	0​	4​	1​
4​	18​	2​	32​	6​	94​	0​	0​	5​
5​	7​	19​	3​	55​	21​	0​	5​	0​
6​	16​	5​	17​	18​	6​	3​	2​	3​
7​	16​	6​	12​	43​	5​	1​	2​	3​
8​	1​	2​	3​	4​	5​	4​	3​	2​

In F2, confirmed with Ctrl+Shift+Enter: =SUMPRODUCT(--(A2:E2=TRANSPOSE(A2:E2)+1))

In G2, confirmed with Ctrl+Shift+Enter: =SUM(MOD(A2:E2,2))

In H2, =5-G2

 

[Rick Rothstein]

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Num1​	Num2​	Num3​	Num4​	Num5​	Seq​	Odd​	Even​
2​	33​	8​	77​	21​	9​	1​	4​	1​
3​	5​	41​	1​	67​	12​	0​	4​	1​
4​	18​	2​	32​	6​	94​	0​	0​	5​
5​	7​	19​	3​	55​	21​	0​	5​	0​
6​	16​	5​	17​	18​	6​	3​	2​	3​
7​	16​	6​	12​	43​	5​	1​	2​	3​
8​	1​	2​	3​	4​	5​	4​	3​	2​

<TBODY>
</TBODY>

In F2, confirmed with Ctrl+Shift+Enter: =SUMPRODUCT(--(A2:E2=TRANSPOSE(A2:E2)+1))

Shouldn't the answer be 5 in the Seq column for 8?

 

[shg]

If that were true, then the answer in the line above would be two.

The formula counts the number of cells that are one greater than each other cell. There are many ways the question could be interpreted; I picked one. Another would be the length of the longest sequence of sequential numbers.

 

[Rick Rothstein]

If that were true, then the answer in the line above would be two.

The formula counts the number of cells that are one greater than each other cell. There are many ways the question could be interpreted; I picked one. Another would be the length of the longest sequence of sequential numbers.

Okay, then why is the result for Row 6 three instead of two (16, 17, 18)?

And yes, I agree that the question can be interpreted in more than one way, which is why it would have been nice if the OP had given an example that met the conditions he asked about along with the answer he would have wanted to see for it.

 

[shg]

Okay, then why is the result for Row 6 three instead of two (16, 17, 18)?

Because 6 is sequential to 5, 17 to 16, and 18 to 17.

 

[Rick Rothstein]

Because 6 is sequential to 5, 17 to 16, and 18 to 17.

Oh, I see how you interpreted now... count the number of pairs of numbers that are sequential. Okay, that is not how I read it, so I was projecting my interpretation on top of what you posted. I would be interested in seeing if your interpretation is actually what the OP had in mind or not.