Using Computed Criteria in Excel for Multiple Conditions

ID: Q77683

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
Microsoft Excel for OS/2 versions 2.2 and 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

SUMMARY

You can use computed criteria to perform database operations in Microsoft Excel based on multiple Boolean criteria. This is a better alternative to the traditional insertion of additional rows and/or columns to accommodate AND and OR type criteria, especially when there are many conditional combinations.

MORE INFORMATION

By using an IF statement, AND/OR functions, and one-dimensional arrays, computed criteria will facilitate multiple conditions. The logical test(s) in the IF statement refer to the different criteria conditions.

Example

The example below extracts those records for which Field1 equals 1, 3, 5, 7, or 9 and Field2 equals 2, 4, or 8. The traditional method of using multiple criteria requires fifteen rows in the criteria to accomplish this. The alternative shown below displays how to test for each of these conditions in a single IF statement using computed criteria.

Enter the following data into a spreadsheet:


A1: Field1 B1: Field2 C1:
A2: 1      B2:  2     C2: =IF(OR(Field1={1,3,5,7,9}),OR(Field2={2,4,8}))
A3: 3      B3:  4
A4: 5      B4:  6
A5: 7      B5:  8
A6: 9      B6:  10

Select cells A1:B6. Choose Set Database from the Data menu. Select cells C1:C2 and choose Set Criteria from the Data menu.

Select cells A1:B1. Choose Copy from the Edit menu. Select cells A8:B8 and choose Paste from the Edit menu.

Choose Extract from the Data menu. Choose OK when the dialog box appears. The following information is extracted:
```
      A8: Field1 B8: Field2
      A9:  1     B9:  2
      A10: 3     B10: 4
      A11: 7     B11: 8 
```

The correct information has been extracted, while minimizing the size of and amount of information required in the criteria.

For additional information, query on the following words:


   computed and criteria and excel

REFERENCES

"User's Guide 1," version 4.0, pages 332-337

"Function Reference," version 4.0, pages 16, 227, 236, 310

"Microsoft Excel User's Guide," version 3.0, pages 374-378

"Microsoft Excel Function Reference," version 3.0, pages 9, 126, 127, 172

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 conditional efficient

Keywords :
Version :
Platform :
Issue type :