Skip to content

GS Challenge 9 – Logical Functions

Welcome to your Spreadsheet Life Google Sheets challenge number nine!

This article will test your ability to work with logical functions within Google Sheets. You can click the link below to download the spreadsheet and attempt each question before you read through all of the answers.

Google Sheets Challenge 9 – Download

Question 1

For question number one, your task is to write two different formulas that will return a final value of TRUE. Also, each formula must use at least one function, and you must reference each value in the range B4:B8.

Question 1 – Solution

For your first formula, I recommend using the OR function.

The OR function evaluates multiple conditions or TRUE/FALSE values and returns TRUE if only one value or condition evaluates to TRUE.

In this case, there are two TRUE values and three FALSE values within the range B4:B8.

So if you select cell D4 and then enter the following formula:

=OR(B4:B8)

You get back the final value of TRUE because there is at least one TRUE value included within the referenced range.

Now for the second formula, I recommend using the AND and NOT functions.

The AND function will return FALSE if any value or condition evaluates to FALSE. Otherwise, the AND function returns TRUE.

The NOT function simply returns the inverse of its evaluated value or condition.

And so, enter the following formula into cell F4.

=NOT(AND(B4:B8))

The AND function finds at least one FALSE value and returns a final value of FALSE.

The NOT function then takes the FALSE value and turns it into a TRUE value.

And just like that, you are finished with question number one.

Question 2

For question number two, your task is to add a function to the formula in cell F5 to remove the error and return the value of “Not Found.”

Question 2 – Solution

There are actually two functions you can use to solve question number two.

  • The IFERROR function
  • The IFNA function

Both functions will work; however, I will be using the IFNA function in this example.

So in cell F5, add the IFNA function and open parentheses before the VLOOKUP function. After the VLOOKUP function, add a comma and “Not Found.”

=IFNA( VLOOKUP(F3,B4:C10,2,FALSE) ,"Not Found")

When the VLOOKUP function cannot find a match (such as in this case), it returns the #N/A error. The IFNA function catches this error and returns a specified value instead.

In this case, the IFNA function returns the value of “Not Found.”

Question 3

For question number three, you must calculate the minimum total sales if the user chooses the minimum for the metric. Otherwise, you must calculate the maximum.

Question 3 – Solution

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

=IF(F3="Minimum", MIN(C4:C10), MAX(C4:C10))

The formula above uses the IF function to test if cell F3 (the cell containing the selected metric) is equivalent to “Minimum.”

If “minimum” is selected, the IF function will evaluate its second argument, and the MIN function calculates the minimum.

Otherwise, the IF function will evaluate its third argument, and the MAX function calculates the maximum.

Question 4

For question four, your goal is to write a formula that automatically calculates the selected metric. Also, your formula should return the value of zero if no metric is selected.

Question 4 – Solution

To solve question four, I recommend using the IFS function.

The IFS function allows you to test multiple conditions and evaluate a specific argument depending on the satisfied condition.

So in cell F5, enter the following formula.

=IFS(F3=H4,AVERAGE(C4:C10), F3=H5,COUNT(C4:C10), F3=H6,MAX(C4:C10), F3=H7,MIN(C4:C10), F3=H8,SUM(C4:C10), 1,0)

In the formula above, the IFS function tests to see which metric is selected in cell F3.

If “Average” (in cell H4) is selected, the function will evaluate the next argument, which contains the AVERAGE function.

If “Count” (in cell H5) is selected, the function will evaluate the next argument, which contains the COUNT function.

And so on.

Finally, if none of the previous conditions are met, the one entered for the second to last argument automatically evaluates to TRUE. As a result, you get back the value of zero as default.

And that officially completes question number four, and your ninth Google Sheets challenge!

Was It Easy or Hard?

Tell me! Did you find this challenge to be pretty easy or rather difficult? Let me know in the comments below. I’d love to hear your feedback.

Also, if you missed any of the other Google Sheets challenges, click the link below to view them all.

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