There are at least NINE simple statistics formulas and functions in Excel that can summarize a list of numbers into something easy to understand.
Often Excel has lists of numbers or money that makes little sense until you add a summary like this:
We have a table called CountbyState and want to know something about the values. Here’s how to add a simple summary with nine Excel formulas.
Total or SUM()
The simplest one first. A total or SUM() of all the values.
=SUM(CountbyState[Value])
SUM() is just one of many total/Sum functions in Excel.
Highest
What’s the highest or maximum value in the list? Use the MAX() function.
=MAX(CountbyState[Value])
Which state has the highest value? Use Xlookup() (365 only) or Vlookup() in earlier versions of Excel.
=XLOOKUP(F4,CountbyState[Value],CountbyState[State])
Xlookup finds the highest value (F4) in the Value column and returns the name in the State column.
Lowest
What’s the lowest or minimum value in the list? Use the MIN() function.
=MIN(CountbyState[Value])
Which state has that highest value? Use Xlookup() (365 only) or Vlookup() in earlier versions of Excel.
=XLOOKUP(F5,CountbyState[Value],CountbyState[State])
Xlookup finds the lowest value (F5) in the Value column and returns the name in the State column.
Average / Mean
Average or Mean is the ‘middle value’ of the list. The total of all the values divided by the number of values. Excel does that for you with the Average() function.
=AVERAGE(CountbyState[Value])
Next to the Average is the State with the value nearest the Average.
=XLOOKUP(0,ABS(CountbyState[Value]-F6),CountbyState[State],,1)
We’ll explain that formula in the next section ….
Median
Median is similar to Average but not the same. The median is the value which has half the values higher and the other half lower.
If the values are evenly distributed, the Average/Mean and Median will be about the same. Like they are in our example. But if the list is skewed (many high or many low values) then they’ll be quite different.
Here’s another example using money values that have some extremely high numbers. See that the Average is now quite different from the Median.
Nearest to the Median
Finding which value is nearest the Median or Average needs a little trickery. Normally Excel can find an exact match but not a near match. Xlookup() in Excel 365 adds a simple way to do a near match.
=XLOOKUP(0,ABS(MoneybyState[Money]-F7),MoneybyState[State],,1)
This compares the difference between each value and median ignoring if it’s higher or lower: ABS(MoneybyState[Money]-F7) … with Zero: 0
That finds the value that’s nearest to the median (by comparing the differences).
The ‘1’ at the end of Xlookup() tells Excel to find the exact match or next largest. In this case that means the value that’s closest to the median in either direction (that’s the ABS() part).
In Excel 2019 and earlier versions, Xlookup isn’t available. Instead use Index() and Match() in an array formula.
=INDEX(ListofValues,MATCH(MIN(ABS(ListofValues-ValuetoFind)),ABS(ListofValues-ValuetoFind),0))
Count
Count sounds obvious but it’s important as a double-check that you’re including everything you should.
=COUNT(CountbyState[Value])
In a famous example, failing to check the Count() meant a whole economic theory went off the rails. See Excel’s effect on economic policy
For a US State list, make sure there are 50 values. We double check that with an IF() test:
=IF(F8=50,"OK?✔","Wrong Count!!")
The check value (50) is hard-coded into the test for this simple model.
Standard Deviation
Standard Deviation measures how much the values vary from the Average/Mean. A low Standard Deviation (nearer zero) means the values are near the mean. The higher it is, the values are more widely ranging.
=STDEV.P((CountbyState[Value]))
We’ve used StDev.P() because we’re examining the whole list/population of states, not a sample.
(If you were wondering why we had a long piece about Standard Deviation in Excel, now you know …)
Standard Error of the Mean
‘Standard Error of the mean’ measures how the sample mean of the data is likely to be compared to the population mean. As such, it doesn’t really make sense in our example because we have a full list of states, not a sample.
We’ve included it so you can see the formula:
=STDEV.S((CountbyState[Value]))/SQRT(COUNT(CountbyState[Value]))
Rank
Column C ranks the values from First to Last.
This is a simple ranking with the highest value ranking #1.
=RANK.EQ([@Value],[Value])
For reverse ranking with lowest as #1, just add a 1 parameter to the end of the Rank.EQ() function like this:
=RANK.EQ([@Value],[Value],1)
Lots of Excel RANK options
Office Watch has many tips for better and more interesting Ranking options
Excel rankings with ordinal numbers, joint, equal and more
Many Ordinal RANK() options in Excel with joint, equal rankings, words and more
Rank.AVG() and how it’s different from Rank() and Rank.EQ() in Excel
Joint vs Equal rankings with Excel’s Rank() and Rank.EQ()
Two ways to show equal rankings with Excel’s Rank() or Rank.EQ()
Excel’s Rank/Rank.EQ function to show order, first, second etc.
Eight SUM functions in Excel … and more
Workbook Statistics finally in Excel
Understand and use Excel’s Standard Error of the mean
Standard Deviation is easy to use and understand in Excel
Plus or Minus sign ± in Word, Excel, PowerPoint and Outlook
Track COVID-19 stats in your own Excel spreadsheet
Is Excel more useful than algebra?
Excel’s order of calculation and BODMAS
