Excel Statistical Functions: Unleash the Power of Data Analysis

AVERAGE Function

Syntax

When I use the AVERAGE function, I stick to the basic structure:

=AVERAGE(number1, [number2], ...)

Arguments

• number1: Required. The first number or cell reference that I include in the average.
• [number2], …: Optional. Additional numbers, cell references, or ranges I might include.

Example

I find that the best way to understand the AVERAGE function is to see how it performs with different kinds of data:

1. Direct Input:

   • If I want to average the numbers 5, 15, and 25, I’ll enter them directly like this:

     =AVERAGE(5, 15, 25)
     

     This would calculate the average as ( (5 + 15 + 25) / 3 ).

2. Cell Range:

   • To average a column, say from A1 to A3, it’s done as:

     =AVERAGE(A1:A3)
     

     It will sum the numbers in those cells and divide by the count of cells.

3. Text-formatted Numbers:

   • Occasionally, numbers might be formatted as text. In such cases, I use a function to convert text to actual numbers before averaging.

4. Ignored Values:

   • If there’s an apostrophe before a cell’s value, making Excel read it as text, it’s ignored in calculations.
     

AVERAGEA

Syntax

=AVERAGEA(value1, [value2], ...)

Arguments

• value1: The first number, logical value, or text-formatted number to include in the average.
• [value2, …]: Optional. Additional numbers, logical values, or text numbers to include in the average.

Example

When I use the AVERAGEA function, I can include various types of data, like logical values TRUE and FALSE, which are counted as 1 and 0, respectively. For instance, let’s take the following set of values: 0 (FALSE), 0 (text), TRUE, 10, 20, and 30.

=AVERAGEA(0, "0", TRUE, 10, 20, 30)

Here’s what shows up:

The outcome? I get an average of 10.17, which makes sense because AVERAGEA takes into account all values, including that TRUE as a 1.

AVERAGEIFS

Syntax

= AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

• average_range: The cells to average.
• criteria_range1: The first set of cells to evaluate based on the first condition.
• criteria1: The first condition that cells must meet to be included in the average.
• criteria_range2 (optional): Another set of cells to test a different condition.
• criteria2 (optional): The second condition for including cells in the average.

Example

I used AVERAGEIFS to figure out the average quantity sold by Peter for product “B”. By inserting the conditions directly into the function, I found the average based on two instances of Peter’s sales. Check out the procedure below:

Next, I wanted to know the average price of fruits named with a “B” and having more than 20 units sold. By including an asterisk in my criteria for the name and the number condition, AVERAGEIFS did the trick again:

COUNT Function

Syntax

= COUNT(value1, [value2], ...)

Arguments

• value1: Must be a number, or a reference to a cell containing a number.
• [value2]: Optional. Can also be a number or a cell reference.

Example

I was trying out COUNT the other day. Just to see how it works, I chose a range from A1 to A10. Guess what? It gave me 8! It totally skipped the logical value and text I had in A3 and A7. However, when I popped a logical value and a number straight into the function, COUNT picked it up.

Here’s what went down:

For the range A1, the outcome is 8, pretty neat:

And with a direct logical value and text in quotations, COUNT didn’t flinch:

COUNTA Function

Syntax

= COUNTA(value1, [value2], ...)

Arguments

• value1: This is what you start with. Drop in a single value, cell reference, or a range of cells you want to count.
• [value2]: This is optional. You can add more values or ranges to count together with the first argument.

Example

I decided to give COUNTA a whirl using a range A1 in my spreadsheet. Check out how it did:

Pretty neat, right? The range had 11 cells, and COUNTA spat out 10—a quick way of telling me that there’s one shy cell not saying anything because, well, it’s empty. The rest are a lively bunch with numbers, some text, a dash of logical values, and a symbol just for kicks.

COUNTBLANK

Syntax

=COUNTBLANK(range)

Arguments

• range: The set of cells where I look for blank spaces.

Example

Let’s say I’m staring down a column of data (A2), looking for gaps. I tap in my COUNTBLANK formula, and it zooms through A2, eyeballing each cell. It counts a total of 3 that are playing hide-and-seek, but one of those, A7, is sneakily empty—only because it’s got a formula that says “shh, pretend I’m not here.”

So, my COUNTBLANK tally shows up as 2, since A4 and A5 are the only true wallflowers without any values or formulas making them blank.

I think it’s super handy for whipping those lazy, empty cells into shape. They can’t hide from me now!

8. COUNTIF

Syntax

= COUNTIF(range, criteria)

Arguments

• range: Cells I count from when they match what I’m looking for.
• criteria: What I’m checking against, precisely.

Example

Say I’ve got a bunch of prices and I want to know how many are over $20. Simple, I use = COUNTIF(A1:A10, ">20").

Using Logical Operators as Criteria

I sometimes find myself grading tests and wanting to know how many scored above 75. Here’s how I do it:

= COUNTIF(A1:A10, ">75")

Remember, any logical operator like “>75” should be quoted.

Using Dates as a Criteria

Maybe I need to see how many team members are off on July 4th. I use the date straight:

= COUNTIF(B1:B10, "7/4/2024")

Excel’s smart that way, turning text into dates for me.

And when I think like Excel, I use numbers like 41819, which is how Excel sees the date:

= COUNTIF(C1:C10, 41819)

Excel’s pretty cool with dates, doesn’t matter if it’s text or a number, it gets the job done.

COUNTIFS

Syntax

Hey there! So, you wanna use the COUNTIFS function in Excel? Here’s how you write it:

= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Yeah, it looks like a regular formula but take it from me, it’s super useful.

Arguments

Diving into what each part means:

• criteria_range1: That’s where you highlight the bunch of cells you’re checking out for your first condition.
• criteria1: This is what you’re hunting for in the first range.
• [criteria_range2]: Optional. If you’ve got another condition, this is where those cells go.
• [criteria2]: Also optional. The second thing you’re trying to spot in the second range.

Make sure each range matches up with its criteria buddy, or you’ll confuse Excel.

Example

Let me walk you through a couple of scenarios where COUNTIFS is your hero:

1. Imagine you’ve got a list of people and you want to count how many ladies over 25 are there. Just set up the first criteria as “Female” and the second as “>25”.

   

2. What if you need to know how many folks named ‘A-something’ are older than 25? That’s where you’d use an asterisk like “A*” to catch all the ‘A’ starters, and again, “>25” to make sure they’re over 25.

   

Trust me, once you get the hang of this, you’ll be COUNTIFS-ing everything!

MAX

Syntax

Here’s how the formula looks:

= MAX(number1, [number2], …)

Arguments

• number1: That’s where you input your first number, or range of numbers. Yep, just pop them into your formula or select a range directly in your spreadsheet.
• [number2]: This is optional. Add this in if you’ve got more numbers or ranges to throw into the mix.

Example

Let me walk you through a quick run-down of using the MAX function:

• First up, punching in numbers straight into the MAX function split by commas. Real simple, like this:
  

• Fancy using a range? I’ve done that too. Select a range and MAX spits out the top number from it, like this champ – 1861:
  

• Be warned – if there’s an error in your data, MAX won’t shy away. It’ll flash an error, just like it did for me here:
  

Remember, you even have variations like MAXA and MAXIFS to play around with. MAXA includes numbers, text, and logical values in its quest for the maximum, while MAXIFS lets you set conditions to filter out the max value.

MIN

Syntax

*= MIN(number1, [number2], …)

Arguments

• number1: Required. The first number, cell reference, or range for which I want to find the minimum value.
• [number2], …: Optional. Additional numbers, cell references, or ranges that I can include in my search for the minimum value.

Example

In my experience using the MIN function, plugging in numbers directly like =MIN(10, 2, 89) is straightforward, and it returns 2, the smallest value.

However, referencing ranges is also pretty handy, for instance, =MIN(A1:A5) might return 1861 if that’s the lowest number in my range from A1 to A5.

I remember one time I included a cell with an error in my range, and instead of a number, the MIN function returned an error, which is something to watch out for.