VBA formula for consecutive values

bump

I'm not sure what you mean by "count consecutive values".
Could you explain further, perhaps useing Excel Jeanie to post an example of the input data and the desired result(s).

Hello Mike, how do you do?

With counting 'consecutive values' I mean the following;


So the result will then be 6.


I hope it's more clear now.

bump

I'm sure this makes perfect sense to you, but I would say it is utterly confusing.

I can see no logical reasoning to why you are counting certain values.
Just letting you know so you can hopefully expand the explanation.

I'm also confused. 2 questions

1 -

You say you want to count consecutive numbers, and so 10, 11 will be counted, and it seems you want to count them twice, like the difference between them can be 1 or -1.

Now you want to count also 60, 62 and the difference between them is 2, not 1. I see you called them 2nd consecutive numbers. Does this means that you want to count the numbers with a difference of 1, -1, 2, -2, or are there other cases (like 3rd consecutive numbers)?

2 -

What happens if you have duplicates?

For ex., replace the 20 in your post with 10. What would be the count now?

Re: Formula for counting 'neighboring' values

Hi snowblizz,

Thanks for the reply, I apologize for the confusion and I'll try to explain it differently.


I'm trying to find a formula that will count 'neighboring' values in a column.
As an example for what I mean with 'neighboring' values;


98 < 2nd neighboring from value 100
99 < 1st neighboring from value 100
100
101 < 1st neighboring from value 100
102 < 2nd neighboring from value 100


So if a column with data looks like this;


85
101 < 1st neighboring from value 100 (=1)
70
25
98 < 2nd neighboring from value 100 (= 1)
100 < 1st neighboring from value 101 and also 2nd neighboring from value 98 (= 2)


So the result will be 4


I hope I've explained it better now, thank you.

You're still not making it clear.

Is your base case 100 - i.e. is it always dependent on the position of other numbers relative to 100?

What is the logic behind 101 being a 1st neighbour (difference of 1) from 100 having a value of 1, but 98 being a 2nd neighbour (difference of 2) also having a value of 1?

Why is 98 considered a 2nd neighbour with a value of 1, but say 25 not a neighbour? By your logic, it would be a 75th neighbour (but then still a value of 1???)

How do you then revert back to 100 and assign it a value of 2 since it is not neighbouring to 100?

Is your column length fixed? Like in the example below, which would then generate your expected sum of 4?

Please try to take a step back and appreciate no-one so far on the board understands your problem and that you really need to break it down into fundamentals.

Hello pgc01,

Q1: Yes exactly, I want to count the numbers with a difference of 1, -1, 2, and -2. (I've should have explained it like that)

Q2: Duplicates will not have to be counted.


Thank you