Choose mean, median, or quantiles for a spreadsheet metric

Author: AILesson7 min setupTested with:ChatGPTReviewed: 2026-08-28

Quick answer

Match the summary statistic to the decision, metric scale, distribution, weighting, and tail risk. Provide: Decision and metric meaning, Distribution and data profile, Reporting and calculation rules. Expected result: A justified statistic set with Excel formulas, distribution checks, and interpretation language.

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Decision and metric meaningDistribution and data profileReporting and calculation rules

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Choose whether mean, median, quantiles, or a combination best answers the stated spreadsheet question.

Decision, metric, unit, grain, population, reconciliation, and affected experience:
[decision]

Sample size, missingness, values, distribution, groups, weights, censoring, and outliers:
[profile]

Exclusions, weighting, percentile convention, groups, thresholds, Excel version, and audience:
[rules]

Start from the estimand: arithmetic total-per-unit expectation, typical ranked observation, tail/service experience, or group comparison. Check whether values are numeric on an interval/ratio scale, whether rows deserve equal weight, whether ratios require denominator weighting, whether zeros are real, and whether skew, multimodality, censoring, small n, or valid extremes change interpretation. Do not choose median merely to hide valid high values or mean merely because it is familiar. Distinguish PERCENTILE.INC from EXC and state the chosen convention; avoid unstable extreme quantiles for small groups. Return: recommended primary and supporting statistics; rationale; Excel formulas with filters/weights; distribution and data-quality checks; group policy; worked calculation; plain-language interpretation; misleading alternatives; and sensitivity analysis.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Decision and metric meaning: Report customer-support response time for monthly staffing and service review. Metric is minutes from ticket creation to first human response. One row per eligible ticket. Staffing planning needs total expected workload, while service review asks what a typical customer and a slow-tail customer experienced. Population is all eligible July tickets, split Standard and Priority. Counts must reconcile to ticket records.
Distribution and data profile: July n=12,400 eligible; 210 response times missing due to logging outage and must remain missing. Values nonnegative. Overall min 1, Q1 18, median 42, Q3 95, P90 240, P95 410, max 2,880, mean 88. Distribution strongly right-skewed. Priority n=620, Standard n=11,780. Some genuine incident tickets exceed 1,000 minutes; 14 impossible negative values were already quarantined and excluded. Tickets closed before response are still eligible with missing response and need separate count.
Reporting and calculation rules: Report valid n and missing n. Primary service statistic median plus P90; mean is supporting for staffing and must include all verified valid extremes. Use PERCENTILE.INC. Do not average monthly or group medians. Show groups separately; Priority service target P90 ≤60 minutes, Standard target P90 ≤300. Microsoft 365. Leadership audience.

Example output

Recommendation: use median 42 minutes as the typical valid-ticket experience and P90 240 minutes as the slow-tail service measure. Report mean 88 minutes separately for expected response-delay workload; it is not the “typical” experience in this right-skewed distribution. P95 and maximum are diagnostic context, not the primary score.

Excel with Table Tickets and Valid flag: =MEDIAN(FILTER(Tickets[ResponseMinutes],Tickets[Valid]=TRUE)); =PERCENTILE.INC(FILTER(Tickets[ResponseMinutes],Tickets[Valid]=TRUE),0.9); =AVERAGE(FILTER(Tickets[ResponseMinutes],Tickets[Valid]=TRUE)). Valid n should be 12,190 only if all 210 missing are the sole nonvalid responses after the 14 negatives were already removed from the 12,400 eligible definition; confirm the count lineage because the wording could instead imply 12,176. Reconcile eligible = valid response + logging missing + closed-before-response + quarantined status according to mutually exclusive definitions.

Group policy: calculate median, P90, mean, valid n, missing n, and target result from raw Priority and Standard rows separately; never average their medians or P90s. Priority P90 is compared with 60 and Standard with 300. With n=620, P90 is reportable; still show its observed value and do not substitute the overall 240.

Interpretation: “Among tickets with a valid recorded response, half received a first human response within 42 minutes and 90% within 240 minutes. The average was 88 minutes because a smaller set of verified long waits pulls it upward. Two hundred ten eligible tickets lack response time, so the distribution may not describe them.” Sensitivity: show all verified extremes and a diagnostic view excluding incident-tagged tickets, but do not replace the official metric without policy approval.

Why this works

1
Starting from the estimand prevents one familiar statistic from answering the wrong operational question.
2
Supporting distribution and tail measures expose trade-offs hidden by any single center value.

Check the result

Does the chosen statistic correspond to expected total, typical case, tail experience, or another explicit target?
Are row weights, ratio denominators, real zeros, missingness, censoring, and group sizes handled explicitly?
Are percentile convention and sensitivity to valid extremes or exclusions stated?

Use it with confidence

Frequently asked questions

Practical answers about when to use this recipe, what to provide, and where human review still matters

What should I prepare before using “Choose mean, median, or quantiles for a spreadsheet metric”?

For “Choose mean, median, or quantiles for a spreadsheet metric,” prepare Decision and metric meaning, Distribution and data profile, and Reporting and calculation rules. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Choose mean, median, or quantiles for a spreadsheet metric” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—A justified statistic set with Excel formulas, distribution checks, and interpretation language—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Choose mean, median, or quantiles for a spreadsheet metric”?

The published test record for “Choose mean, median, or quantiles for a spreadsheet metric” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

More ways to explore