Worksheet formula syntax

[TKA]

I stumbled upon one thing I cannot resolve:

I have floor area calculations in a large worksheet everything is working when I add area calculations, when I need to subtract for some reason its not working, I tried other variations too with the same result:

 

=CRITERIAAREA((L='A101-1ST FLOOR') & (C='Z-area-FAR-X - C')+(C='Z-area-FAR-X - R'))   - this works

 

=CRITERIAAREA((L='A101-1ST FLOOR') & (C='Z-area-FAR-X - C')-(C='Z-area-FAR-X - R'))   - this doesn't

 

 

[Pat Stanford]

I think it was just luck that the plus worked because you are using the criteria wrong.  You can't add or subtract criteria you can only do Boolean logic of AND (&) or OR (|) on them.

 

So the first one should be something like ((L='xxx' & ((C='123') OR (C='456'))). This will get you the area of objects on layer xxx that are in either class 123 or class 456.

 

Depending on what you are trying to do the second could be similar or much harder.  If you just want the area of objects in class 123 and not in class 456 then

 

((L='xxx') & ((C='123') & NOT (C='456'))). will get you those obejcts

 

If you really want to get the area of objects in class 123 and subtract from them the area of objects in class 456 then you need to use two different CriteriaArea statements

 

=CriteriaArea((L='xxx') & (C='123')) - CriteriaArea((L='xxx') & (C='456'))

 

There is no way to do math inside a single CriteriaArea (or most other functions) in worksheets.

 

Ask again if you need more help.

[TKA]

thanks, that works, I thought I did tried that but must have done something wrong and got confused by the "luck"