compass heading calculation

I am currently trying to produce a document which will calculate the errors in our compass systems. This data is taken from carrying out a compass swing and then taking readings from various cardinal points. All is simple in the calculations until passing through north. When calculating deviations between our datum heading and compass heading one result is subtracted from the other (datum – compass). For example:

Datum 359.90°
Compass 000.15°
Deviation -0.25°

We have to input the compass reading as 360.15° because otherwise Excel will return a deviation (or difference) of 359.75° (359.9° – 000.15°) and not
-0.25°. The trouble is 360.15° isn’t a proper compass heading, it should be 000.15°. This needs to work both ways ie, 000.15° – 359.90° returning a positive 0.25° and not a -359.75°. Is there a way of making these calculations in a sort of ‘base 360’ sense?

Another problem I face is that all compass headings we work with are displayed at two decimal places due to the accuracy of our systems. How can I get all compass headings displayed as ###.##? For example:

I would like headings displayed as follows:
000.15°
002.25°
028.30°
060.00°
180.00°

We take readings every 30° from north and we are only normally looking at errors up to about 5° and never more than 10°. Therefore each heading can be looked at individually. For example, at 30° the format will always be 0##.## to return 030.15. The same goes for 60° and 90°. The trouble I face is that if I use a custom number, I lose the opportunity to select how many decimal places I would like. An example of this is the heading 030.00° formatted as 0##.##° will return 030.° and not 030.00°.
I would really appreciate any help on these matters.

Thanks.

Jaypee,

The problem (as I'm sure you know) is that normal representations of angle do not wrap from 359 to 0. Engineers have long used a method called BAMS (binary angle measurement system), which store angle as scaled integers. For simplicity, consider just a single-byte representation:

&H00 = 360 * 0/256 = 0
&H01 = 360 * 1/256 = 1.40
&H80 = 360 * 128/256 = 180

Here's the key: &H80 can equally be regarded as either an unsigned value (128) or a two's-complement value (-128) -- and either interpretation gives the exact same result. So

&HFF (which is either -1 or 255) + 1 = 0 -- angles wrap!.

The upshot is that you can do regular binary math, ignoring overflows, and it all just 'falls out.' How cool is that?

Instead of bytes, engineers usually use Integers, so they get 32-bit precision, about 0.005 degrees.

Unfortunately, VBA does not allow overflows (oh, why??), so you can't used this method in a straightforward fashion.

The routine below uses 31 bits of a Long (yielding a fixed precison of 1.7E-7 degrees) to compute compass deviations. Use it as a worksheet function like this:

=CompassDev(A1,B1)

where A1 contains the datum in degrees, and B1 the compass in degrees.

I hope it is useful to you.

Please Login or Register  to view this content.

You say that the maximum deviation will be 10° so, assuming datum heading is in A1 and compass heading in A2 try this formula to give you the deviation

=MOD(A1+10,360)-MOD(A2+10,360)

custom format readings as 000.00°

You truly are the Daddy, long legs. A huge thanks to all that replied. Such a simple formula and where I started but never put the error into the equation. The worksheet works a treat and saves me approx 4-5 hours per compass swing.

Much appreciated,

Regards

Jaypee.

p.s. You may hear from me again soon! Bloody newbies!