Alert when consecutive numbers in a list exceed 5

[Patricia]

Good morning

I have thousands of records, and need to be alerted when, consecutive
numbers (in a particular column) exceed 5. It has to be consecutive numbers
(not sorted).

eg
data 1
data 1
data 8
data 8
data 8
data 8
data 8
data 8

In this case the number 8 exceeds 5.
Would really appreciate help on this, as at the moment it is really time
consuming.

Thanking you in anticipation.

 

[Barb Reinhardt]

Have you thought about using conditional formatting for this?

Select the cells with the numbers you want to compare.

Format -> Conditional Formatting

Condition 1 -> Formula is
=ABS(B1-B2)>=5

Change the format for the cell so that you can visually find the change.

Alternatively, you could add a column with a calculation of the delta
between the two values and use AutoFilter to find the values that exceed
your limits.

 

[Guest]

Say you have used cols A & B for data and numbers, then in C6 insert:

=IF((B6=B5)+(B6=B4)+(B6=B3)+(B6=B2)+(B6=B1)=5,1,"") amd copy down

If there are 6 consecutive numbers in column B the function will return a 1,
otherwise a blank.

 

[Patricia]

Thank you so much for your quick response.

I found that Gary"s Student solution does the job so well - I have used
Barb's idea on the formula cells using conditional formatting to have the
result highlighted.

Thank you both again for your help, really appreciate it.