Managing ship headings: 2 problems

Occasional Visitor

Problem 1

  1. I have a large set of numbers, representing a ship's direction (heading) taken every minute for 6 months.
  2. It is a large ship resting in the water and has no engines. It changes direction subject to the wind, tide and wave direction.
  3. The ship cannot change heading by more than 20 Deg in one minute. 
  4. I have inserted a formula in a column adjacent to this list of numbers which gives the difference between the previous heading and the current heading to give a 'rate of turn'. This is the change in heading occurring over 1 minute. The formula is a simple =sum(B4-B3). So lets say B4 = 48 Deg and B3 = 40 Deg, the sum would be 8 Deg (the numbers could also be negative if turning in a counterclockwise direction).
  5. Obviously, many of the numbers are repeated over the 6 month period.
  6. I am trying to get the frequency of the same numbers coming up over this period ie; number 'x' is repeated 2000 times, number 'y' repeated 15000 times etc. This can be as a percent of the total data set or as total number within the data set.

Problem 2

  1. Following on from above, sometimes the ship's heading is changing across the 360 Deg heading ie; It may be sitting at 358 deg one minute, and turn clockwise to 7 deg the next. 
  2. Using the above cell formula, the sum would be: B4 = 358 and B3 = 7 so Sum= 351 Deg.
  3. Clearly, the vessel did not swing 351 degrees in one minute, it swung 9 Deg.
  4. It may also be sitting at say 5 Deg one minute and turn counterclockwise to 349 deg the next. 
  5. Also using the above cell formula, the sum would be: B4 = 5 and B3 = 349 so Sum= -344 Deg.
  6. Clearly, the vessel did not swing  counterclockwise -344 degrees in one minute, it swung 16 Deg.
  7. What would the formula be to solve this?



1 Reply


Problem 1) You can use a pivot table for this.


Problem 2) =B4-B3+IF(B4-B3<-20,360,IF(B4-B3>20,-360,0))