# Average the degrees on a compass

#### airborneholt

##### New Member
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

### Excel Facts

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

#### Norie

##### Well-known Member
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
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,

#### airborneholt

##### New Member
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
Is this for wind direction?

#### shg

##### MrExcel MVP
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)