Using excel to count number of revolutions

[TOKYOJ1]

Hi,

I work with planetary data and would like to seek help to use excel to count the number of revolutions each time 360 degrees is passed. The catch is there is retrograde motion causing the degrees to move backwards.

Example data (Degrees):
15
100
125
150
158
180
165
120
90
130
190
240
357
8 (1st revolution)
25
70
100
180
270
350
10 (2nd revolution)
30
80
100
90
77
55
54
52
40
60
120
190
210
340
5 (3rd revolution)
......

Thanks in advance

 

[Joe4]

I think we need a little more guidance from you on how to determine when this should happen.

We need to determine what is just a "rollback" and what is a revolution.
How would you recommend we do that? Is there some threshold of an acceptable "rollback" amount?
Or, is there some point at which it would not ever roll back (i.e. after 300 degrees)?

So we just need your help in determining acceptable logic as to when the revolution occurs by looking at the data.

 

[TOKYOJ1]

Hi,

Thanks for your reply.

We need to determine what is just a "rollback" and what is a revolution.
One revolution is counted each time 360 degrees is passed. If 360 degrees is passed 10 times it will be counted as 10 revolutions

How would you recommend we do that? Is there some threshold of an acceptable "rollback" amount?

90
240
357
8 (1st revolution)​

IF rollback occurs at 8 degrees back to 357, 355, 348, 320, the rollback amount would exceed 300 and would not be acceptable.
My logic would be "if threshold>300 then no revolution counted when next 360 is passed"


Or, is there some point at which it would not ever roll back (i.e. after 300 degrees)?​

<strike></strike>No, the rollback may occur anytime

Thanks for your help
​

 

[sheetspread]

You didn't show the 3rd revolution passing 360 degrees. We can assume, but seeing the subsequent numbers helps more.

 

[TOKYOJ1]

You didn't show the 3rd revolution passing 360 degrees. We can assume, but seeing the subsequent numbers helps more.

Yes, it passed 360 degrees hence 3rd revolution counted.

 

[Joe4]

OK, let's say that your data is in the range A1:A36.
Then enter this formula in cell B2 and copy down for all rows:

=IF(A1-A2>300,MAX(B$1:B1)+1,"")

 

[TOKYOJ1]

OK, let's say that your data is in the range A1:A36.
Then enter this formula in cell B2 and copy down for all rows:

=IF(A1-A2>300,MAX(B$1:B1)+1,"")

I tested this and it worked well.
Modified the formula a bit so I can keep track of the revolutions running down the column
B1=0

=IF(A1-A2>300,MAX(B$1:B1)+1,B1)​

Thanks again!

 

[Joe4]

You are welcome!