Skip to content

GS Challenge 11 – Math Functions

Welcome to Google Sheets challenge number eleven!

In this article, I will provide four questions to test your knowledge of Math functions in Google Sheets.

Click the link below to get your copy of the spreadsheet!

Google Sheets Challenge 11

Question 1

For question number one, your task is to calculate the circumference and area of a circle with a radius of 2.5.

Question 1 – Solution

Let’s start with the circumference. The equation to calculate the circumference of a circle is:

C = 2πr

So in cell C5, enter the following formula.

= 2 * PI() * C4

Two is multiplied by the PI function, which returns the value of Pi, and that result is then multiplied by the radius in cell C4.

This formula gives you the circumference, which is approximately 15.71.

Let’s calculate the area next. The equation to calculate the area of a circle is:

A = πr²

So in cell C6, enter the following formula.

= PI() * POWER(C4, 2)

The PI function returns the value of Pi, and the POWER function raises the radius in cell C4 to the power of two.

These two numbers are multiplied together to give you the final area, which is approximately 19.63.

Question 2

For question number two, your goal is to create random sales numbers for each agent between 100 and 1000, rounded to the nearest 100.

Question 2 – Solution

To solve question two, I recommend using two functions.

  • RANDBETWEEN
  • ROUND

The RANDBETWEEN function can generate random numbers between two specified values, and the ROUND function can round the number for us.

So in cell C5, enter the following formula.

=ROUND(RANDBETWEEN(100, 1000), -2)

The RANDBETWEEN function generates a random number between 100 and 1000, and the ROUND function rounds this number to the nearest 100.

The minus two for the second argument of the ROUND function forces the function to round to the nearest hundred.

To complete the rest of the numbers, use the fill handle to copy the formula down for each sales agent.

Question 3

For question three, your task is to count the number of sales agents from division A, with at least 500 in total sales.

Question 3 – Solution

You can use the COUNTIFS function to solve question three.

In cell F5, enter the following formula.

=COUNTIFS(C5:C12, "A", D5:D12, ">=500")

The COUNTIFS function tests to see which cells in C5:C12 contain the value of A, and the function also tests to see which cells in D5:D12 contain values greater than or equal to 500.

The COUNTFIS function returns a final count of those rows that meet both conditions.

Question 4

For question four, you must calculate the total sales for those sales agents from divisions AB, and C.

To solve question three, enter the following formula into cell F5.

=SUM(ARRAYFORMULA(SUMIF(C5:C12, {"A","B","C"}, D5:D12)))

The SUMIF function checks to see which cells in C5:C12 contain one of the values from the array provided for the second argument. {“A”, “B”, “C”}

Also, since the SUMIF function uses an array for the second argument, then the function must be wrapped within the ARRAYFORMULA function.

The SUMIF function then returns an array of values which include the sum of sales from:

  • Division A
  • Division B
  • Division C
=SUM( {700, 800, 1700} )

This array passes to the SUM function, and the SUM function adds these numbers together to calculate the final result.

How Did It Go?

So tell me – how did you do?

Did you find these questions to be fairly easy, or did you find them to be rather difficult? Leave me a comment below to let me know.

Also, if you missed any of the other Google Sheets challenges, you can view them all using the link 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