Skip to content

Excel Challenge 12 – Logical Functions

Welcome to your Spreadsheet Life Excel challenge number twelve!

This article will test your ability to work with logical functions within Microsoft Excel.

You can click the link below to download the spreadsheet and attempt each question before you read through all of the answers.

Excel Challenge 12 – Download

Question 1

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

Question 1 – Solution

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

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

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

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

=AND(B5:B9)

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

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

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

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

And so, enter the following formula into cell F5.

=NOT(OR(B5:B9))

The OR function finds at least one TRUE value and returns a final value of TRUE.

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

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

Question 2

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

Question 2 – Solution

To solve question two, enter the following formula into cell F6.

=IF(F4="Maximum", MAX(C5:C11), MIN(C5:C11))

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

If “Maximum” is selected, the IF function will evaluate its second argument, and the MAX function calculates the maximum.

Otherwise, the IF function will evaluate its third argument, and the MIN function calculates the minimum.

Question 3

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

Question 3 – 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 use the IFNA function in this example.

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

=IFNA(VLOOKUP(F4, B5:C11, 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 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 F6, enter the following formula.

=IFS(F4=H5,AVERAGE(C5:C11),
F4=H6,COUNT(C5:C11),
F4=H7,MAX(C5:C11),
F4=H8,MIN(C5:C11),
F4=H9,SUM(C5:C11),
1,0)

To add line breaks to your formula, use ALT+Enter.

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

If “Average” is selected, the function will evaluate the next argument, which contains the AVERAGE function.

If “Count” 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 ‘1’ 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 12th Excel 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 other Excel 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