Adding the OR branches
A record may satisfy both criteria rows. The selected database record contributes once, not once per branch.
Describe the numeric records selected by criteria
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter separate formulas in N5, O5 and P5 using A1:E13, field header H6 and criteria H1:K3. Return the numeric arithmetic mean, minimum and maximum, in that order. Include each matching source record once, including numeric zeroes and signed observations. Text, numeric-looking text, booleans and blanks in the chosen field are not numeric observations. If no numeric observations remain, return genuine #DIV/0! for the mean and numeric zero for both extremes. Do not replace the missing mean with a zero or a label.
Read and edit the applied criteria
All nonblank conditions on one row use AND; the two rows use OR. A blank row matches every record. These controls store literal text, so =East stays a criterion rather than becoming an executable formula.
| Criteria row | Region | Status | Units rule 1 | Units rule 2 |
|---|---|---|---|---|
| 2 | =East | =Posted | >=2 | <=5 |
| 3 | =West | =Released | >=0 | <=9 |
Applied field:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: N5, O5, P5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Report mean and extremes for the actual selected numeric observations.
THE QUICK ANSWER01
Enter separate formulas in N5, O5 and P5 using A1:E13, field header H6 and criteria H1:K3. Return the numeric arithmetic mean, minimum and maximum, in that order. Include each matching source record once, including numeric zeroes and signed observations. Text, numeric-looking text, booleans and blanks in the chosen field are not numeric observations. If no numeric observations remain, return genuine #DIV/0! for the mean and numeric zero for both extremes. Do not replace the missing mean with a zero or a label.
N5
=DAVERAGE($A$1:$E$13,$H$6,$H$1:$K$3)O5
=DMIN($A$1:$E$13,$H$6,$H$1:$K$3)P5
=DMAX($A$1:$E$13,$H$6,$H$1:$K$3)For the example data
N5 32.8125O5 -26.25P5 120
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Criteria are data, not query fragments
Region and Status criteria use text prefixes, wildcard patterns or explicit =exact text. Comparisons are case-insensitive; * matches multiple characters, ? matches one, and ~ escapes a wildcard or tilde. A leading apostrophe is an Excel entry aid for storing a criterion beginning with = as literal text; it is not part of the stored criterion.
Keep records and numbers distinct
A criteria rule selects records first. Numeric summary functions then ignore nonnumeric values in the selected field. A matching record containing text "18" or TRUE is different from a numeric observation, and a unique literal "#NUM!" is different from the actual multiple-match error.
Keep the supported boundary visible
Units inputs are whole numbers within ±10,000. Amount accepts numeric cents within ±1,000,000, literal text, Boolean values or blanks. Numeric comparisons in this course apply only to Units. Text ordering/not-equal criteria, calculated criteria columns, external databases and typed source errors require separate rules.
Read the full step-by-step walkthrough
- Include the database headers
The first row supplies field names. The database range includes all twelve original records, not only the visible matches.
- Read each criteria row as a rule
Across H:K, all nonblank conditions on a row must hold. Across rows 2 and 3, either complete rule can select a record. Matching both rows does not create a second observation.
- Constrain the same Units field twice
The repeated Units headings apply a lower and an upper bound to one record. A completely blank criteria row matches all records.
- Select the result field
H6 is the field header Amount or Units. Keep it as a worksheet reference so the same formulas work when the selected field changes.
- Return the DAVERAGE result
Select numeric observations without coercing text or booleans. Each source record contributes at most once.
- Return the DMIN result
Select numeric observations without coercing text or booleans. Each source record contributes at most once.
- Return the DMAX result
Select numeric observations without coercing text or booleans. Each source record contributes at most once.
Things to watch
Treating both rows as AND
All columns on a row combine with AND. Different rows are alternatives.
Returning the first matching record
DGET is an exactly-one-record operation. Returning a first match conceals ambiguity.
About this example
Original criteria records and independent selection/type/cardinality rules checked against the real browser formula engine. Native Excel and Sheets recalculation/edit/save remain separate verification steps.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.