Average the degrees on a compass

[airborneholt]

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

 

[Norie]

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...

 

[shg]

Maybe ...

      -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]

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]

Is this for wind direction?

 

[shg]

Maybe a UDF:

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)