Excel: Reference Requirements of Computed Criteria

ID: Q61290

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

SUMMARY

When computed criteria is used in Microsoft Excel, the references in the formula must contain both of the following:

A relative reference to the first record in the database

An absolute reference to any other cells on the worksheet to be included in the formula

MORE INFORMATION

For example, the following worksheet has a defined database of cells A1:B9:


   A1:   Date   B1: Amount C1:     D1: 1/1/90
   A2:  12/1/89 B2:  100   C2:     D2:
   A3: 12/15/89 B3:  150   C3:     D3:
   A4: 12/19/89 B4:  125   C4:     D4:
   A5: 12/29/89 B5:  155   C5:     D5:
   A6:   1/5/90 B6:  175   C6:     D6:
   A7:  1/12/90 B7:  150   C7:     D7:
   A8:  1/20/90 B8:  200   C8:     D8:
   A9:  1/27/90 B9:  185   C9:     D9:

To find all records in the database with dates later than January 1, 1990, the following computed criteria can be used


   =A2>DATE(90,1,1)

where A2 is a relative reference to the Date field in the first record of the database.

Furthermore, to find all records in a database with dates later than the date value of cell D1, the following computed criteria can be used


   =A2>$D$1

where A2 is a relative reference to the Date field in the first record of the database and $D$1 is an absolute reference to the cell that contains the comparison date value.

REFERENCES

For more information on computed criteria, see pages 371-378 in the "Microsoft Excel User's Guide" version 3.0 manual. If you are using Excel 2.20, see pages 171-172 in the "Microsoft Excel Reference" version 2.2 manual.

Additional query words: 2.20 4.00

Keywords :
Version :
Platform :
Issue type :