Mean direction through 360 degrees?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for the hep, but I need to explain myself better.

I am trying to get a mean direction for a set of data which varies through 360 degrees. If I just use the Average function this will not work, for example the actuan mean of 1, 0 and 359 degrees will be 0 degs, using the Average function in Excel would give me a result of 120 degs, which is not what I am looking for. Any ideas???


Plaese can anyone help?

I have some columns of directional data (0 to 360 degs) and need to calculate a mean direction, can anyone help? I'm sure it can't be that hard.
 
Lange,

You can try this, its not elegant, but it works! (based in
a vector sum)

Assume you data is in A1:A10

In B1 through B10 use: =COS(A1*2*PI()/360) (changing row
number as appropriate)
In C1 through C10 use: =SIN(A1*2*PI()/360) (changing row
number as appropriate)

In B11: =SUM(B1:B10)
In C11: =SUM(C1:C10)

The average angle is =ATAN(C11/B11)*360/2/PI()

Hope it helps!

Felipe
-----Original Message-----
Thanks for the hep, but I need to explain myself better.

I am trying to get a mean direction for a set of data
Click to expand...
which varies through 360 degrees. If I just use the
Average function this will not work, for example the
actuan mean of 1, 0 and 359 degrees will be 0 degs, using
the Average function in Excel would give me a result of
120 degs, which is not what I am looking for. Any ideas???
Plaese can anyone help?

I have some columns of directional data (0 to 360 degs)
Click to expand...
and need to calculate a mean direction, can anyone help?
I'm sure it can't be that hard.
 
I don't know if this is what you want but, with your data in A1:A4,
(assuming it may be more than 3 items), have a *helper* column B (which may
be hidden), with the formula
=(A1<=180)*A1+(A1>180)*(360-A1)
in B1 and copied down to B4.

In B5 enter the formula
=SUMIF(B1:B4,"<>"&0)/COUNTIF(B1:B4,"<>"&0)

and finally in the cell you want the mean direction answer enter the formula
=IF(B5>0,B5,360+B5)

HTH

Sandy



--
to e-mail direct replace @mailintor.com with @tiscali.co.uk
Lange said:
Thanks for the hep, but I need to explain myself better.

I am trying to get a mean direction for a set of data which varies through
Click to expand...
360 degrees. If I just use the Average function this will not work, for
example the actuan mean of 1, 0 and 359 degrees will be 0 degs, using the
Average function in Excel would give me a result of 120 degs, which is not
what I am looking for. Any ideas???
Plaese can anyone help?

I have some columns of directional data (0 to 360 degs) and need to
Click to expand...
calculate a mean direction, can anyone help? I'm sure it can't be that hard.
 
Woops!

Tested one thing and posted another!

Make that fist formula in A1
=(A1<180)*A1+(A1>180)*(A1-360)


.......... I think

Sandy
 
Hi:

=MOD(SUM(A1:A100),360)/COUNT(A1:A100)

Regards,

Vasant.

Lange said:
Thanks for the hep, but I need to explain myself better.

I am trying to get a mean direction for a set of data which varies through
Click to expand...
360 degrees. If I just use the Average function this will not work, for
example the actuan mean of 1, 0 and 359 degrees will be 0 degs, using the
Average function in Excel would give me a result of 120 degs, which is not
what I am looking for. Any ideas???
Plaese can anyone help?

I have some columns of directional data (0 to 360 degs) and need to
Click to expand...
calculate a mean direction, can anyone help? I'm sure it can't be that hard.
 
Vasant Nanavati said:
Hi:

=MOD(SUM(A1:A100),360)/COUNT(A1:A100)

Regards,

Vasant.

through
360 degrees. If I just use the Average function this will not work, for
example the actuan mean of 1, 0 and 359 degrees will be 0 degs, using the
Average function in Excel would give me a result of 120 degs, which is not
what I am looking for. Any ideas???
Click to expand...

In practical situations, there seems only one difficulty in averaging
compass bearings which occurs when the mean is sufficiently close to 0 and
the variation large enough to give individual values on both sides of zero.
The OP has essentially pointed out that if we had two measurements, say -6
and +4, the average would be -1: a compass bearing of 359. The actual
compass bearings would be 354 and 4 with a calculated average of 179. To get
the true average, every bearing greater than 180 should have 360 subtracted
from it and, if the resulting average is negative, 360 should be added to
it. This last is MOD( value, 360).

You could use two averages, sines and cosines of the angles and then ATAN2
but the previous method would probably be faster if the number of
measurements is large.

I find it difficult to think of an experiment where measured compass
bearings would have a range of something like plus and minus 180 degrees but
I would be pleased if the OP could enlighten me. To tell the truth, the only
situation that occurs to me to need this sort of averaging is perhaps a
class of recruits taking compass bearings of a lighthouse from a boat!
 
Back
Top