Skip to content

GS Challenge 7 – Statistical Functions

Welcome to your seventh Google Sheets challenge!

In this article, I give you four questions to test your skills at working with statistical functions within Google Sheets.

You can download the spreadsheet to test yourself using the link below.

Google Sheets Challenge 7 – Download

Give it a try and see how many questions you can solve correctly.

Question 1

For question one, your task is to rank the sales agents from one to ten. The agent with the highest total sales should be ranked one.

Question 1 – Solution

To solve question one, you can use the RANK function. The RANK function ranks a numerical value among several numerical values, which is what you want in this case.

So starting in cell D5, type in the following formula.

=RANK(C5, $C$5:$C$14)

The RANK function takes the value from C5 and compares that value to all of the other total sales numbers selected for the second argument.

The RANK function then returns that value’s rank, with the largest value receiving a rank of one.

Note that you want to make the second argument an absolute reference. You make this an absolute reference so that it will not change when you copy this formula down for the other sales agents.

And once you have completed the first formula, use the fill handle to copy the formula down for all the other sales agents.

Question 2

For question two, your goal is to calculate the average (arithmetic mean) total sales for all sales agents assigned to the West region.

Question 2 – Solution

To complete question two, you can use the AVERAGEIF function.

The AVERAGEIF function allows you to calculate the arithmetic mean for a set of numbers based on a specified condition.

Start by selecting cell F5, then enter the following formula.

=AVERAGEIF(C5:C14, "West", D5:D14)

The AVERAGEIF function looks at the range C5:C14 (the 1st argument) and checks to see which cells contain the value of “West” (2nd argument).

The AVERAGEIF function then calculates the arithmetic mean for all those values in D5:D14 (3rd argument) that share a row with the cells containing “West.”

As a result, you end up with the average total sales for all sales agents assigned to the West region.

Question 3

For question three, your goal is to write a formula to list the top three sales agents with the highest total sales.

Question 3 – Solution

To solve question three, you will need two functions.

You will use the LARGE function to get back the top three total sales numbers from the data. You will then use the XLOOKUP function to return the sales agents associated with the top three sales numbers.

With that in mind, select cell F5 and enter the following formula.

=XLOOKUP(LARGE($C$5:$C$14, E5), $C$5:$C$14, $B$5:$B$14)

Keep in mind that there are absolute references used throughout this formula.

The LARGE function searches through the range of C5:C14 and returns the first largest value due to the one in cell E5.

This value passes to the XLOOKUP function, which then looks for the value and returns the corresponding sales agent.

You can now use the fill handle to copy the formula down for spots two and three.

Once you do, the LARGE function takes the two and three and returns the second and third largest values, respectively.

The XLOOKUP function then works just like before and returns the corresponding sales agents.

Question 4

For question four, you must calculate the maximum total sales among sales agents assigned to a western region who have generated over 250 new leads.

Question 4 – Solution

To solve question four, you will have to use the MAXIFS function.

The MAXIFS function returns the maximum values based on several specified conditions.

With that in mind, select cell G5 and enter the following formula.

=MAXIFS(E5:E14, C5:C14, "*West*", D5:D14, ">250")

The MAXIFS function looks at the range C5:C14 to see which values contain “West” somewhere within the text.

The wildcard characters (*) enable the function to search for text that contains “West.”

The MAXIFS function also looks at which numbers exceed 250 within the range D5:D14.

Finally, the MAXIFS function calculates the maximum among the numbers in E5:E14 that sit in rows that meet both specified criteria.

As a result, you get back the maximum value for those sales agents who meet the criteria.

How Did You Do?

So tell me! How did you do?

Did you find these questions to be pretty hard, or were they not so bad?

Leave me a comment below to let me know how you did!

Also, if you missed one of the first three Google Sheets challenges, you can find them listed below.

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