michaelk Posted January 19, 2010 Share Posted January 19, 2010 2 questions for worksheet/report gurus: 1. Is an OR allowed in a report? For example this will count the objects in question on one layer: =COUNT(((L='Some Layer') & ('Plug In type'.'Field Name'='Field Value'))) If one wanted to count the total of the objects meeting the criteria on 2 layers is there a way to do this: =COUNT(((L='Some Layer') OR (L='Some Other Layer') & ('Plug In type'.'Field Name'='Field Value'))) [That doesn't work. Is there a syntax thing I'm missing?] I'm currently resorting to =COUNT(((L='Some Layer') & ('Plug In type'.'Field Name'='Field Value'))) + COUNT(((L='Some Other Layer') & ('Plug In type'.'Field Name'='Field Value'))) Which brings me to ... 2. Is there a way to get the formula field at the top of the worksheet/report window to wrap? Once the formulas get long they get difficult to edit. Thanks in advance michaelk Quote Link to comment
Pat Stanford Posted January 19, 2010 Share Posted January 19, 2010 Michael, A Vertical Pipe character | is a logical or in worksheets. If you put two criteria with the same category in a criteria dialog box it will generate an OR (and if you look at the formula bar after you click OK to the criteria you will see the pipe character. For your count, the following should work. Make sure you get your parens correct. =COUNT((((L='Some Layer') | (L='Some Other Layer')) & ('Plug In type'.'Field Name'='Field Value'))) I don't know of a way to get the formula bar to wrap. Quote Link to comment
michaelk Posted January 19, 2010 Author Share Posted January 19, 2010 Thanks, Pat! Have you ever tried referencing another worksheet to fill in layer or field value names? ie, in the above example: =COUNT(((L='Ref Worksheet:A2') | (L='Ref Worksheet:A2')) & ('Plug In Type'.'Field Name'='Ref Worksheet:B1'))) I know that syntax doesn't work, but is there a way to do it? michaelk Quote Link to comment
michaelk Posted January 19, 2010 Author Share Posted January 19, 2010 Hmmmmm "|" is producing unexpected results... I know there are 18 objects in Layer1 and 0 objects in Layer2. =COUNT(((L='Layer1') & ('Plug In type'.'Field Name'='Field Value'))) + COUNT(((L='Layer2') & ('Plug In type'.'Field Name'='Field Value'))) returns a value of 18 =COUNT(((L='Layer1') | (L='Layer2') & ('Plug In type'.'Field Name'='Field Value'))) returns a value of 130 Confused... michaelk Quote Link to comment
Pat Stanford Posted January 19, 2010 Share Posted January 19, 2010 Hmmmmm "|" is producing unexpected results... I know there are 18 objects in Layer1 and 0 objects in Layer2. =COUNT(((L='Layer1') & ('Plug In type'.'Field Name'='Field Value'))) + COUNT(((L='Layer2') & ('Plug In type'.'Field Name'='Field Value'))) returns a value of 18 Yep. If your counts are correct that is what is should be doing. =COUNT(((L='Layer1') | (L='Layer2') & ('Plug In type'.'Field Name'='Field Value'))) returns a value of 130 I told you watch your parentheses. What your formula is saying is: return the count of all the objects whose Layer is Layer 1 or whose Layer is Layer 2 and have a Field Value. I think what you want is: =COUNT((((L='Layer1') | (L='Layer2')) & ('Plug In type'.'Field Name'='Field Value'))) You need to use the parens to group the Layer 1 and Layer 2 parts into a single logical statement. Translated to words the above says return the count of all the objects on Layer1 or Layer 2 with Field Value. See the difference? Quote Link to comment
michaelk Posted January 19, 2010 Author Share Posted January 19, 2010 LOL Have you thought about producing an annotated translation of the manual? Thanks again MK Quote Link to comment
Gerrit Posted January 22, 2010 Share Posted January 22, 2010 Michael, If you put two criteria with the same category in a criteria dialog box it will generate an OR (and if you look at the formula bar after you click OK to the criteria you will see the pipe character. In v2010, the OR operator will indeed be used when a search is done on "type". But "layer" and "class" being the first choice, the formula will use the AND operator - unless I miss something. Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.