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; (30646.88, « »)

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.

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
    • "WM" <warrenmmmmm .excel.todaysummary.com. 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