Using excel to count number of revolutions

TOKYOJ1

New Member
Joined
Aug 14, 2017
Messages
30
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,412
Office Version
365
Platform
Windows
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.
 
Last edited:

TOKYOJ1

New Member
Joined
Aug 14, 2017
Messages
30
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
You didn't show the 3rd revolution passing 360 degrees. We can assume, but seeing the subsequent numbers helps more.
 

TOKYOJ1

New Member
Joined
Aug 14, 2017
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,412
Office Version
365
Platform
Windows
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:
Code:
=IF(A1-A2>300,MAX(B$1:B1)+1,"")
 

TOKYOJ1

New Member
Joined
Aug 14, 2017
Messages
30
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:
Code:
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,412
Office Version
365
Platform
Windows
You are welcome!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,078
Messages
5,466,480
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top