Advice on how count cells with specific criteria..

Hello talented VB bods

Okay here goes, this is based on roulette.

Column A has the below:

0
32
15
19
4
21
2
25
17
34
6
27
13
36
11
30
8
23
10
5
24
16
33
1
20
14
31
9
22
18
29
7
28
12
35
3
26
(this list/range totals 37 different numbers which have to be in this particular order and bear in mind the wheel is CIRCULAR, so the number next to 26 is 0, which you will find at the top of this list.)

If the first number spun was 34 and the next number was 4 it becomes this:

Start num: 34 End num: 4, which gives me a count of -5 (NOTE it is not a count of 6 as i do not include the 34 as a counted number). So you start from 34 then count "17, 25, 2, 21, 4" which is a -5 count. (see list above)

Why -5 you ask? because i am counting anti-clockwise on the wheel and not clockwise. For example if it was

Start: 4 End: 34, then this would be a count of 5 and not -5, because 4 is 5 "counts" clockwise of 34 based on the roulette wheel.

Hope your still with me here...and heres the cruncher...

The count cannot be greater than 18 or -18. For example.

If the start number was 0 and the end number was 5, if you count the list starting from the top at 0, when you get to 5, you will see that it is a count of 19 (remember not to include the 0 as a counted number).

In this case i would count in the opposite direction which would be a count of -18, which is acceptable. Remember - the count cannot be greater than 18 or -18.

Lets do one more example If the start num was 21 and the end number was 22, based on the list above if i counted clockwise it would be a count of 23, which is a no no, so i count in the opposite direction (anti-clockwise) which gives me -14...try it and you'll understand what i am on about, remember the wheel is circular so when you get to the last number in the list (26) go back up to the top of the list(0) and continue counting.

So..... my issue at last is to automate this in a function within Excel. At present, i am able to count the number of cells between the start and end numbers, but cannot figure out how to ensure excel understands IF the count is greater than 18 or -18, then to basically count in the opposite direction.

This question is obviously for the HARDCORE excel Visual Basic gods, so your help is appreciated. I understand there may be some q's so please ask away.

Thanks for any help, greatly appreciated,

vbwanab

Welcome to the forum,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.