How to count the data in ranges for compass points
 

Hi,
Can someone help me write the syntax that will correctly count the data in
ranges for compass points...
Something like this:
=countif(v4:v800, "22.5") +? or -? countif(v4:v800, "<67.5") etc
would equal northeast
=countif(v4:v800, "67.5") -? countif(v4:v800, "<112.5") would equal
east....but this is where it goes wrong. One can only apply the second
argument to the range that goes down to the first.
Sanity check: after going through the range of compass points the sum of the
results should be 796.
Compass points (it make sense when you draw a circle and mark off the
segment ranges):
N 337.5 to 22.5
NE 22.5 to 67.5
E 67.5 to 112.5
SE 112.5 to 157.5
S 157.5 to 202.5
SW 202.5 to 247.5
W 247.5 to 292.5
NW 292.5 to 337.5
Natalie.

How to count the data in ranges for compass points
 

=countif(v4:v800, ""&22.5) - countif(v4:v800, ""&67.5)

would count everything greater than 22.5 and less than or equal to 67.5.
You'd have to think about if you want to include or exclude values that are
exactly 22.5 or exactly 67.5.

"="&22.5 will include 22.5 in the count
""&22.5 will exclude 22.5
""&67.5 will include 67.5
"="&67.5 will exclude 67.5

or try sumproduct, which may be more intuitive

=Sumproduct(--(v4:v80022.5), --(v4:v800<67.5))
to count everything between 22.5 and 67.5. Changing the comparison
operators to = and <= will include 22.5 and 67.5 respectively.





"WM" wrote:

Hi,
Can someone help me write the syntax that will correctly count the data in
ranges for compass points...
Something like this:
=countif(v4:v800, "22.5") +? or -? countif(v4:v800, "<67.5") etc
would equal northeast
=countif(v4:v800, "67.5") -? countif(v4:v800, "<112.5") would equal
east....but this is where it goes wrong. One can only apply the second
argument to the range that goes down to the first.
Sanity check: after going through the range of compass points the sum of the
results should be 796.
Compass points (it make sense when you draw a circle and mark off the
segment ranges):
N 337.5 to 22.5
NE 22.5 to 67.5
E 67.5 to 112.5
SE 112.5 to 157.5
S 157.5 to 202.5
SW 202.5 to 247.5
W 247.5 to 292.5
NW 292.5 to 337.5
Natalie.

How to count the data in ranges for compass points
 

"WM" <warrenmmmmm @ gmail.com wrote...
....
ranges for compass points...
Something like this:
=countif(v4:v800, "22.5") +? or -? countif(v4:v800, "<67.5") etc would
equal northeast
=countif(v4:v800, "67.5") -? countif(v4:v800, "<112.5") would equal
east....but this is where it goes wrong. One can only apply the second
argument to the range that goes down to the first.

360 degrees in a circle, 8 compass points (because you're only looking at
NE, SE, SW, NW rather than NNE, etc.), 360 / 8 = 45, but points are centered
within their intervals, so +/- 45/2 degrees either side of the points. I'll
use the convention that exact equality on the boundaries always resolves to
major compass points, so exactly 337.5 and 22.5 are both North, exactly 67.5
is East, exactly 292.5 is West, etc.

Use a table. I'll name it Points.

__0.0000__0__N
_22.5001__1__NE
_67.5000__2__E
112.5001__3__SE
157.5000__4__S
202.5001__5__SW
247.5000__6__W
292.5001__7__NW
337.5000__8__N

Then you could use formulas like

=SUMPRODUCT(--(LOOKUP(YourRangeHere,Points)="N"))

If and when you want to expand to 16 points (e.g., NNE), all you'd need to
do would be expand the table. The formulas referring to it could remain
as-is. Their values may change if something that used to be NE became ENE.