Using Cell References in Computed Criteria in Excel PSS ID Number: Q51226 Article last modified on 02-26-1993 PSS database name: W_eXceL
2.x 3.00 | 2.x 3.00
WINDOWS | OS/2
Summary:
When using a single cell for a comparison value in a computed criteria range, you must use an absolute reference for the single cell (and a relative reference for the first cell in the comparison field).
If you want to compare two individual columns in a database, you must use two relative references (indicating the topmost entry in each column).
More Information:
Consider the following database (A1:D6) and column E:
A B C D E
1: Name Age Color Number 24 2: Mike 18 Blue 23 3: John 25 Brown 7 4: David 24 Green 13 5: Ben 32 Red 3 6: Cheryl 28 Puce 37
In a criteria range (for extraction or DFunctions), the criterion =B2>D2 would compare the Age and Number fields to each other for each individual record (B2 with D2, B3 with D3, B4 with D4, etc.) and return those records whose Age entry is greater than the Number entry (John, David, Ben).
The criterion =B2>$D$2 would compare the Age field with cell D2 and return those records whose Age entry is greater than the value in cell D2 (23: John, David, Ben, Cheryl).
The criterion =B2>E1 would compare B2 to E1, B3 to E2, B4 to E3, etc., and return only those records whose Age entry is greater than the value used in the comparison. Remember that blank cells are treated as having a value of 0 (zero), so B3:B6 would be compared to 0 (E2:E5 are blank). If a comparison with E1 (only) is desired, then the criterion should be changed so that E1 is absolute, that is. =B2>$E$1.
Additional reference words: 2.00 2.00 2.01 2.10 2.1 3.0 3.00
Copyright Microsoft Corporation 1993.
