ES

Excel · Chapter 9 · Concept lesson · 7 min read

Typical, spread, and money over time

Totals hide how days differ. The mean, median and standard deviation describe a column in three numbers, and PMT does the same job for a loan: one rule, repeated every month.

The idea

A total says how much. It does not say what a normal day looks like, or how much days differ. For that you need two kinds of number: one for the centre and one for the spread.

The centre has two common versions. The mean (AVERAGE) adds the values and divides by how many there are, so every value pulls on it, the big ones hardest. The median (MEDIAN) sorts the values and takes the middle one, so it only cares about the order, not how far away the extremes are. When the values are balanced they agree. When a few huge days stretch one end, the mean follows them and the median stays with the crowd.

Figure 1

Kitchen revenue per day, June 1 to 14

$0 $625 $1,250 $1,875 $2,500 Revenue Jun 1: $1,078Jun 2: $673Jun 3: $382Jun 4: $511Jun 5: $218Jun 6: $290Jun 7: $2,364Jun 8: $336Jun 9: $405Jun 10: $486Jun 11: $19.99Jun 12: $1,265Jun 13: $521Jun 14: $373 Jun 1Jun 3Jun 5Jun 7Jun 9Jun 11Jun 13 mean 637.13 median 445.19

Step 1 of 4 Fourteen days of Kitchen revenue. Most days sit between $200 and $700. One day, June 7, is $2,364.

  1. Fourteen days of Kitchen revenue. Most days sit between $200 and $700. One day, June 7, is $2,364.
  2. The mean adds all fourteen days and divides by fourteen: $637.13. It uses every value, and that includes the big one.
  3. The median is the middle of the sorted days. With fourteen values, it is halfway between the 7th ($404.50) and the 8th ($485.88): $445.19. Seven days are below it and seven above.
  4. Only three of the fourteen days beat the mean (June 1, 7 and 12), while seven beat the median. The mean describes a day that almost never happens. The median describes a normal one.

Why it exists

Imagine telling the buyer "Kitchen sells $637 a day". It is true as arithmetic. But on 11 of 14 days it sold less than that, and on a typical day it sold about $445. If the buyer plans stock for $637 a day, they will over-order most of the time. One big day, perhaps a promotion or a single large order, bent the average.

The same problem appears everywhere a few large values sit among many small ones: order values, salaries, delivery times, home prices. For these, the median is usually the honest "typical". The mean still has its place, because it is the one that adds back up: mean × days is the total. Use the mean when you will multiply it back, and the median when you describe a normal case.

How it works under the hood

Spread: how far from the centre

Two categories can have the same mean and still be very different to run. One sells $700 every single day. Another sells $0 on most days and $7,000 on a few. The average is identical, but only one of them is predictable. The standard deviation puts a number on that. In steps:

  1. Take each day's distance from the mean.
  2. Square the distances, so they cannot cancel out and big gaps count extra.
  3. Average the squares, then take the square root to get back to dollars.

The result reads as "a typical day is about this far from the mean". The Kitchen days above have a mean of $637 and a standard deviation of $594: days routinely land hundreds of dollars away from the mean, which says the mean is a poor forecast for any single day. A small standard deviation, say $50 on a $700 mean, would say the opposite.

Excel has two versions: STDEV.S and STDEV.P. They differ in step 3. STDEV.P divides by n, the number of values, and is for when the cells are the whole group. STDEV.S divides by n - 1 and is for when the cells are a sample of something bigger, which is nearly always the case when you want to predict. Default to STDEV.S.

Two columns that move together

CORREL(range1, range2) measures whether two columns rise and fall together, on a scale from -1 to 1. Near 1: when one is high, the other tends to be high. Near 0: no straight-line link. Near -1: they move opposite ways. It tells you the pattern is there, never why. Traffic and orders correlating does not mean one caused the other: both may follow the season.

Money over time

Finance functions follow the same logic of one repeated rule. A loan charges interest on what you still owe, every month. PMT finds the equal payment that, with that interest, brings the balance to zero at the end. It needs the rate per period, the number of periods, and the amount. The single most common mistake is mixing units: the rate must be per period, and the count of periods must be in the same period.

Figure 2

How PMT turns a yearly rate into a monthly payment

  1. =E1-E2 loan: 38,400 - 5,000 = 33,400
  2. E3/12 7.5% a year = 0.625% a month
  3. E4*12 4 years = 48 payments
  4. =-PMT(E3/12, E4*12, E7) payment: 807.58 a month

Step 1 of 4 A loan is an amount, a rate and a length. The amount comes first: the price minus the down payment, $33,400.

  1. A loan is an amount, a rate and a length. The amount comes first: the price minus the down payment, $33,400.
  2. Interest is charged every month, so the rate must be a monthly rate: the yearly 7.5% divided by 12.
  3. The length must be counted in the same unit as the rate, in months: 4 years are 48 payments. A yearly rate with a monthly count is the most common finance mistake.
  4. PMT finds the one equal payment that pays off the loan and its interest after 48 months: $807.58. Over four years that is $38,763.62, so the interest cost is $5,363.62.

The sign is a convention. PMT returns a negative number because it treats the payment as money going out. Put a minus in front when you want to show the payment as a positive cost.

The other finance functions reuse the same ingredients and solve for a different one: NPER asks how many months a given payment takes, FV what a savings plan reaches, and PV how big a loan a given payment can carry. That last one is also what Goal Seek finds when you ask "which amount makes the payment equal $1,000?". Each function solves for one unknown and takes the rest as inputs.

Rounding

ROUND(x, 2) rounds to cents; INT always rounds down to a whole number, so INT(4.9) is 4 and INT(-4.1) is -5. ABS removes the sign. Round at the end, for display, not in the middle of a calculation, or small errors pile up.

Common misconceptions

"The average is the typical value"

Only when the values are balanced around it. With one big day, the mean sits above almost every real day. Put the mean and the median side by side: if they differ a lot, a few values are stretching the data, and the median is the better "typical". This is the quickest skewness check there is.

"A low standard deviation means the numbers are small"

The standard deviation measures how much values differ from each other, not how big they are. A column of 1,000,000, 1,000,001 and 999,999 has a tiny deviation. Compare it with the mean to judge it: $594 on a mean of $637 is large, $594 on a mean of $60,000 is small.

"A high CORREL proves one thing causes the other"

CORREL says two columns move together. Ad spend and orders can both rise in November because it is Black Friday season, not because one drives the other. Use the number to find relationships worth investigating, and use something other than a correlation to claim a cause.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

On June 7, Kitchen revenue was $2,363.72. Suppose it had been $500 instead and every other day stayed the same. What happens to the mean ($637.13) and the median ($445.19)?

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.