Average the degrees on a compass

airborneholt

New Member
Joined
Feb 9, 2009
Messages
8
I'm trying to figure out a way to average directions given in compass degrees- where 0 is north, 90 is east, 180 is south, 270 is west and 360 is back to north.

It is easy until I get two compass readings that are north east and north west-- for example, an average of 350 degrees and 10 degrees would be 360 degrees, but mathematically, if you average them, you would get 180 degrees.

Does anyone have any suggestions?

Thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,566
Office Version
365
Platform
Windows
What do you mean by 'average' exactly?

Couldn't you consider degrees over 180° as 'negative'?

So when you get to 180° and move to 190° you are now at -170°.

Then when you move round to 350° you are at -10°.

You can get the 'negative' by subtracting 360.

PS That's probably completely and utterly wrong but...
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,747
Office Version
2010
Platform
Windows
Maybe ...

Code:
      -A- B- --C--
  1   355 22   8.5
  2   270 45 337.5
  3   315 90  22.5
In C1 and copied down,

=MOD(DEGREES(ATAN2(COS(RADIANS(A1)) + COS(RADIANS(B1)), SIN(RADIANS(A1)) + SIN(RADIANS(B1)))), 360)
 

airborneholt

New Member
Joined
Feb 9, 2009
Messages
8
Thanks for the quick replies.

Norie, I see where you're going, but SHG got it perfectly.

SHG, is there an easy way to modify this to average the values in columns? I may need to average A1-A5 or A3-A10...

Thanks
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,566
Office Version
365
Platform
Windows
Is this for wind direction?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,747
Office Version
2010
Platform
Windows
Maybe a UDF:

Code:
Function AvgComp(av As Variant) As Double
    Const pi        As Double = 3.14159265358979
    Const D2R       As Double = pi / 180#
    Dim v           As Variant
    Dim dCos        As Double
    Dim dSin        As Double
 
    For Each v In av
        If VarType(v) = vbDouble Then
            dCos = dCos + Cos(D2R * v)
            dSin = dSin + Sin(D2R * v)
        End If
    Next v
    
    If dCos <> 0# Or dSin <> 0# Then
        AvgComp = WorksheetFunction.Atan2(dCos, dSin) / D2R
    End If
    If AvgComp < 0# Then AvgComp = AvgComp + 360#
End Function
E.g., =AvgComp(A1:A10)
 

Watch MrExcel Video

Forum statistics

Threads
1,100,205
Messages
5,473,145
Members
406,849
Latest member
AndreasMs

This Week's Hot Topics

Top