Skip to content

Excel Challenge 10 – Statistical Functions

Welcome to Excel Challenge number ten. In this article, I will present four questions to test your skills in working with statistical functions in Excel.

You can download the spreadsheet to test yourself by clicking the link below.

Excel Challenge 10 – Download

Question 1

For your first question, your task is to write a formula to calculate the number of cells containing text values.

Question 1 – Solution

To solve question one, I recommend using two functions.

  • COUNT
  • COUNTA

You will use the COUNTA function to count all non-empty cells and the COUNT function to count all the cells with numbers.

You will then subtract these two values to calculate the number of cells with text values.

So in cell E5, enter the following formula.

=COUNTA(B5:C12) - COUNT(B5:C12)

As a result, you get the number of cells containing text values.

Question 2

For question two, your task is to use the data to calculate the indicated metrics.

Question 2 – Solution

We will take question two one step at a time.

To calculate the maximum, select cell F4 and enter the following formula.

=MAX(C5:C12)

The MAX function references all the total sales numbers and calculates the maximum.

To calculate the minimum, select cell F6, and enter the following formula.

=MIN(C5:C12)

The MIN function references all the total sales numbers and calculates the minimum.

To calculate the median, select cell F8, and enter the following formula.

=MEDIAN(C5:C12)

The MEDIAN function references all the total sales numbers and calculates the median.

To calculate the mean, select cell F10, and enter the following formula.

=AVERAGE(C5:C12)

The AVERAGE function references all the total sales numbers and calculates the arithmetic mean.

To calculate the 3rd smallest value, select cell F12, and enter the following formula.

=SMALL(C5:C12, 3)

The SMALL function calculates the nth smallest value. The 3 (used for the second argument) forces the SMALL function to return the 3rd smallest value.

Question 3

For question three, you must calculate the average (arithmetic mean) total sales for all western regions.

Question 3 – Solution

To solve question three, I recommend using the AVERAGEIF function.

Select cell E5, and enter the following formula.

=AVERAGEIF(B5:B12, "*West*", C5:C12)

The AVERAGEIF function looks in B5:B12 and tests which cells contain the text value of “West.” The wildcard characters (asterisks) on either side of “West” ensure that any text before or after “West” will count as a match.

The AVERAGEIF function then calculates the average for those numbers in C5:C12 that correspond with a western region.

Question 4

Question four asks you to calculate how many sales agents from the North region are among the top five performers.

In other words, you must range the sales agents from one to ten. You then have to count how many top five performers are from the North region.

Question 4 – Solution

Your first step is to rank the sales agents from one to ten.

To pull this off, select cell E5 and enter the following formula.

=RANK.EQ(D5, $D$5:$D$14)

Note that the reference for the second argument is an absolute reference.

The RANK.EQ function will rank the selected value in D5 in comparison to the other sales numbers in D5:D14.

You can then use the fill handle to copy this formula down for the remaining sales agents.

Now that you’ve calculated the rank for each sales agent, you can move on to step two – getting the final count.

Select cell G5 and enter the following formula.

=COUNTIFS(C5:C14, "North", E5:E14, "<=5")

The COUNTIFS function will test which cells in C5:C14 contain “North,” and it will also test which cells in E5:E14 contain values less than or equal to five.

The COUNTIFS function will then return a count of the rows containing cells that pass both tests.

As a result, you get back the number of sales agents from the North region among the top five performers.

How Did You Do?

So tell me – how did it go?

Leave me a comment below, letting me know which questions you got right and which might have been a struggle. I’d love to get your feedback.

That said, thanks for giving yourself a challenge, and be sure to check out the other Excel challenges as well.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top