BF Style Criteria
BF style criteria are similar to Excels but their behavour has been extended with the use of the "?" operator to signify the value being tested. The ways to input criteria are:
- Simple Criteria using >,<,>=,<=, <> and =. These can either be a text argument (">10") or a written in a cell that is referred to in the function ie >10. The criteria are entered in a cell as text (even if you just type in >2, Excel actually understands this internally as text), or entered directly as a text string in a function such as DoIf. You can preface with a " or leave Excel to interpret them as text.
- Simple Values that imply an "equals to", eg 10 written in a cell. These can be entered either as text, date or number or directly into the argument list of a function such as DoIf
- Developed Criteria, which use the value tested against in more complex ways, perhaps using Excels own functions, such as MOD(?,10)=0. In this way you can use AND() and OR(), eg AND(MOD(?,10)=0,MOD(?,3)=0). The criteria are entered in a cell as text or directly into the functions argument list, and you can preface with a " or leave Excel to interpret them as text. The value tested against is the "?" and the function internally subsitutes the value into any place (and there can be more than one) where there is a "?". Developed criteria do not accept cell references or range names however. Instead, you have to convert the contents of what are comparing against to text, for example, with the range names CurYear and EndYear: =DoIf(0,$P$8:$P$38,S8:S38,"and(?>"&TEXT(CurYear,"#")&",?<="&TEXT(EndYear,"#")&")")
- A List of Criteria. A range of cells is permitted as Criteria for further OR conditions (the default). So each cell represents an "OR" condition. If you want each cell to be an AND condition, see the variable CritOpts.
|