Red = CEO salary
QUARTILE.INC — then apply the IQR fence rule to formally test whether $135K is an outlierLower fence = Q₁ − 1.5 × IQR
Upper fence = Q₃ + 1.5 × IQR
Any value outside these bounds is a statistical outlier. This is the method Excel's boxplot uses internally.
Reporting the mean is technically correct — but it may mislead 24 out of 25 staff about what they can expect to earn. Reporting the median avoids this — but some stakeholders will question why you're not using "the average." You need evidence-based justification.
What happens to the mean, SD, and CV when you remove the CEO? The difference between the full-dataset and trimmed statistics is your quantitative evidence for the recommendation.
There is no single "correct" measure. The mean is correct for summing to a total (payroll budget). The median is correct for representing a typical individual. Always state which you're using and why.
As part of this year's compensation review, we are sharing the following salary information for transparency.
The median annual salary at Meridian is $56,000. This means half of all employees earn below this figure and half earn above it. We are reporting the median rather than the arithmetic mean ($60,680) because one salary sits significantly above the rest and would give a misleading impression of what a typical Meridian employee earns.
The middle 50% of salaries fall between $50,500 and $66,500, giving an interquartile range of $16,000.
It discloses both the median and the mean, explains why the median is preferred, and provides the IQR range — so no individual figure can be accused of being cherry-picked. This is the standard expected of a professional accountant preparing financial communications.
AVERAGE and STDEV.SFinance salaries average $72K — Operations average $49K. Of course Finance has a larger absolute SD — its values are higher. CV asks a different question: relative to what each team earns, which team has more internal inconsistency?
A high CV within a team suggests salaries are not consistently banded by role or seniority. This can indicate: ad-hoc salary negotiations, unexplained pay gaps (potentially by gender or tenure), or blurred role boundaries where junior and senior staff receive very different pay for similar work.
Your recommendation needs to state: which team, which metric you're using to justify it, and what specific action the Committee should take (e.g., commission a salary banding review, request justification for the $37K spread).
| Year | Payroll change | Growth factor (1 + R) |
|---|---|---|
| 2020 | +8.0% | 1.0800 |
| 2021 | +12.0% | 1.1200 |
| 2022 | −5.0% | 0.9500 |
| 2023 | +15.0% | 1.1500 |
| 2024 | +6.0% | 1.0600 |
GEOMEAN on the growth factors to find the CAGR. Show why you cannot apply GEOMEAN directly to the percentage rates=1400000*(1+CAGR)^3Payroll compounds. A 15% increase in 2023 applies to a base that was already reduced 5% in 2022. The arithmetic mean treats each year's rate as if it applies to the same base — it overestimates what actually happened. The geometric mean correctly accounts for compounding.
In Excel: =1400000*(1+B9)^3 where B9 holds your CAGR
The question is not just "what is the number" — it's "is the number a problem?" Calculate the headroom (budget cap minus projection) and flag whether the trend is sustainable if growth accelerates.
| ID | Training hrs (X) | Annual errors (Y) |
|---|---|---|
| A | 12 | 15 |
| B | 18 | 12 |
| C | 25 | 8 |
| D | 30 | 5 |
| E | 15 | 14 |
| F | 22 | 10 |
| G | 28 | 6 |
| H | 10 | 17 |
| I | 20 | 11 |
| J | 35 | 3 |
| K | 8 | 19 |
| L | 32 | 4 |
CORREL(X,Y) and COVARIANCE.S(X,Y). What does the sign of the covariance tell you?Use the trend to estimate: at 28 hours, predicted errors ≈ ? Compare to 20 hours. Multiply the reduction by 12 staff × $800. Compare to $45,000 investment.
A correlation coefficient summarises the linear relationship. A scatterplot reveals whether the relationship is actually linear, whether outliers exist, and whether there are distinct clusters. If the data showed a curved pattern, r would understate the true relationship — and no amount of Excel formula output would reveal this without the graph.
Removing Employee J changes r from −0.9953 to −0.9938 — a difference of only 0.0015. The conclusion is not driven by a single high-leverage observation. Every point follows the trend closely. This strengthens the business case for the training investment.
A rigorous professional recommendation separates what the data shows (strong association) from what it proves (causation). Approving $45K on correlational evidence alone is defensible — but only if you state the assumption being made and commit to a follow-up evaluation to confirm the effect.
| Invoice amount ($) | Freq (f) | Midpoint (m) |
|---|---|---|
| 0 – <500 | 12 | 250 |
| 500 – <1,000 | 18 | 750 |
| 1,000 – <2,000 | 14 | 1,500 |
| 2,000 – <5,000 | 8 | 3,500 |
| 5,000 – <10,000 | 5 | 7,500 |
| Total | 57 | — |
SUMPRODUCT or helper columnsHigh standard deviation in invoice amounts signals a heterogeneous population — large and small invoices coexist, making it more likely that a random sample misses high-value anomalies. Auditors use CV to compare dispersion across client populations and benchmark years.
Clients often provide trial balance summaries, ageing schedules, or purchase reports in banded form. Auditors routinely extract estimates from these tables before requesting raw data — particularly in preliminary analytical procedures.
Benchmark CV = 400/1200 × 100 = 33.3%. Calculate your client's CV and compare. If client CV is materially higher, this increases audit risk because proportionally more value sits in unpredictable, high-variance transactions.
| Stat | Decision signal |
|---|---|
| Mean > Median | Right skew — report median |
| Value > Upper fence | Formal outlier — investigate or remove |
| CV > 30% | High relative spread — check for banding issues |
| r < −0.8 | Strong negative link — consider causal study |
| Geomean < Arith mean | Volatile growth — geomean is the correct CAGR |
| Approx. error < 5% | Grouped data estimate acceptable for preliminary use |
1. State the result: "The CV is 18.2%."
2. Interpret it: "This indicates above-average relative dispersion."
3. Make the call: "Recommend a pay equity audit for the Finance team."
A statistical result that doesn't end in a decision or recommendation is incomplete. Every number in professional practice is evidence for or against an action.
Use .S when your data is a sample from a larger group (default in most business settings). Use .P only when you have data on every member of the population — rare in practice.