Compass bearings

[CWatters]

I have some data pairs containing compass bearings and wind speed.

Q1 Is there an easy way to find entries within say 20 degrees of a
bearing ? Sounds easy but "within 20 degrees of north" would cover the
range 340-360 and 0-20.

Q2 What I'm really trying to do is work out the average speed when the
wind is from a particular direction. So I guess I need to include wind
speed data in an average only if the bearing is within range.

Any suggestions on how best to go about this?

 

[Bruce Sinclair]

I have some data pairs containing compass bearings and wind speed.

Q1 Is there an easy way to find entries within say 20 degrees of a
bearing ? Sounds easy but "within 20 degrees of north" would cover the
range 340-360 and 0-20.

Q2 What I'm really trying to do is work out the average speed when the
wind is from a particular direction. So I guess I need to include wind
speed data in an average only if the bearing is within range.

Any suggestions on how best to go about this?

Without trying it ( ), perhaps a mod function might help here ?

 

[Gord Dibben]

OP might find something useful at Chip's site.

http://www.cpearson.com/excel/latlong.aspx


Gord Dibben MS Excel MVP

 

[Bruce Sinclair]

OP might find something useful at Chip's site.

http://www.cpearson.com/excel/latlong.aspx
Gord Dibben MS Excel MVP

Very likely IME. Thanks for that.

 

[Ron Rosenfeld]

I have some data pairs containing compass bearings and wind speed.

Q1 Is there an easy way to find entries within say 20 degrees of a
bearing ? Sounds easy but "within 20 degrees of north" would cover the
range 340-360 and 0-20.

Q2 What I'm really trying to do is work out the average speed when the
wind is from a particular direction. So I guess I need to include wind
speed data in an average only if the bearing is within range.

Any suggestions on how best to go about this?

There's probably an easier way to express this, but the following should return TRUE if the entry is within WITHING degrees of BEARING.

Bearing and Within are expressed as degrees 0-360.

Lower = Bearing - Within
Upper = Bearing + Within

=OR(AND(A2>=Lower,A2<=Upper),AND(A2>=(360+Lower),A2<=(360+Upper)),AND(A2>=Lower-360,A2<=Upper-360))

The following returns 1 in that circumstance:

=SUMPRODUCT((A2>=(Bearing-Within+{360,0,-360}))*(A2<=(Bearing+Within+{360,0,-360})))



To obtain average windspeed, for values within the range, you could use a formula like:

=SUMPRODUCT((WindDirection>=(Bearing-Within+{360,0,-360}))*
(WindDirection<=(Bearing+Within+{360,0,-360}))*windspeed)/
SUMPRODUCT((WindDirection>=(Bearing-Within+{360,0,-360}))*
(WindDirection<=(Bearing+Within+{360,0,-360})))

Where WindDirection and windspeed are columns containing the obvious.

 

[Ron Rosenfeld]

I have some data pairs containing compass bearings and wind speed.

Q1 Is there an easy way to find entries within say 20 degrees of a
bearing ? Sounds easy but "within 20 degrees of north" would cover the
range 340-360 and 0-20.

Q2 What I'm really trying to do is work out the average speed when the
wind is from a particular direction. So I guess I need to include wind
speed data in an average only if the bearing is within range.

Any suggestions on how best to go about this?

Here's another fun solution.

Given a column of wind directions, and an adjacent column of wind speeds:

Select the data, then Insert/Chart and select a Radar Chart
You may have to specifically set the Wind Direction as the "X-axis"

 

[Salmon Egg]

I have some data pairs containing compass bearings and wind speed.

Q1 Is there an easy way to find entries within say 20 degrees of a
bearing ? Sounds easy but "within 20 degrees of north" would cover the
range 340-360 and 0-20.

Q2 What I'm really trying to do is work out the average speed when the
wind is from a particular direction. So I guess I need to include wind
speed data in an average only if the bearing is within range.

Any suggestions on how best to go about this?

While I understand your words, I have no idea what your sentences mean.
My guess is that the problem is readily soluble using trigonometry over
short distances and spherical trigonometry over geodetic distances. In
any event, Excel has plenty of capability. To start, ust formulate your
problem better.

Bill

 

[CWatters]

Here's another fun solution.

Given a column of wind directions, and an adjacent column of wind speeds:

Select the data, then Insert/Chart and select a Radar Chart
You may have to specifically set the Wind Direction as the "X-axis"

Great Ron. Thanks for the ideas.

 

[Ron Rosenfeld]

Great Ron. Thanks for the ideas.

Glad to help. Thanks for the feedback.