### Tags: compass, correctly, countif, excel, microsoft, msdn, points, ranges, software, syntax, v4v800, write

# How to count the data in ranges for compass points

On Microsoft » Microsoft Excel

3,875 words with 2 Comments; publish: Fri, 23 May 2008 18:13:00 GMT; (306109.38, « »)

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.

*http://excel.questionfor.info/q_microsoft-excel_241498.html*

All Comments

Leave a comment...

- 2 Comments
- =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:v800>22.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.

>

>

#1; Fri, 23 May 2008 18:14:00 GMT

- =countif(v4:v800, ">"&22.5) - countif(v4:v800, ">"&67.5)
- "WM" <warrenmmmmm .excel.questionfor.info. 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.

#2; Fri, 23 May 2008 18:15:00 GMT

- "WM" <warrenmmmmm .excel.questionfor.info. gmail.com> wrote...