Eight easy statistics in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

There are simple 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 eight Excel formulas.

Total

The simplest one first.  A total or SUM() of all the values.

=SUM(CountbyState[Value])

Highest

What’s the highest or maximum value in the list?  Use the MAX() function.

=MAX(CountbyState[Value])

Which state has that 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 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 everything you want.

=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 last week, now you know.  It was a prelude to this article.

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.

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

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address