Excel: Reference Requirements of Computed Criteria

 Last reviewed: November 29, 1994
Article 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.

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.20 3.00 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.