Skip to content

Email Newsletter Challenge 5

This week will test your knowledge of logical functions. I also threw an extra challenge in there this time.

You can download the spreadsheet challenge using the links below.

The Challenge

Your challenge this week is to write a formula in cell C3 to calculate the average, count, or sum of sales depending on the selection in cell C5. And return zero otherwise.

For your extra challenge, perform this same calculation without using the IF or IFS functions.

Think you can solve this one? Give it your best try!

Solution 1

Both solutions work the same with in Excel and Google Sheets.

Simply select cell C3, and enter the following formula.

=IF(C5=B8, AVERAGE(G4:G14), IF(C5=B9, COUNT(G4:G14), IF(C5=B10, SUM(G4:G14), 0)))

Here is how it works.

You have three nested IF functions. The first IF function checks to see if the selected metric is equal to the value in B8 (Average). If that is true, then the AVERAGE function will calculate. If not, then the second IF function calculates.

The second IF function checks to see if the selection is equal to B9 (Count). If so, then the COUNT function will calculate. Otherwise, the third IF function calculates.

The third IF function checks to see if the selected value is equal to B10 (Sum). If that is true, then the SUM function calculates. If not, you get back zero.

Solution 2

For solution two, you are not allowed to use the IF or IFS functions. That means a great alternative here would be to use the SWITCH function.

Select cell C3, and enter the following formula.

=SWITCH(C6, B9, AVERAGE(G5:G15), B10, COUNT(G5:G15), B11, SUM(G5:G15), 0)

The SWITCH function begins by evaluating the first argument. In this case, you only have a single cell reference that evaluates to the selected value in cell C6.

From here, the SWITCH function checks to see if the second argument is equivalent to the first. If it is, then the function calculates the third argument.

=SWITCH(C6, B9, AVERAGE(G5:G15), B10, COUNT(G5:G15), B11, SUM(G5:G15), 0)

If the arguments don’t match, then the SWITCH function looks to see if the first and fourth arguments match. If so, then the fifth argument is calculated.

=SWITCH(C6, B9, AVERAGE(G5:G15), B10, COUNT(G5:G15), B11, SUM(G5:G15), 0)

Again, if the arguments don’t match, then the SWITCH function checks to see if the first and sixth arguments match. If so, then the seventh argument is calculated.

=SWITCH(C6, B9, AVERAGE(G5:G15), B10, COUNT(G5:G15), B11, SUM(G5:G15), 0)

This trend continues until the very last argument (which must make for an even number of arguments in total). If none of the previous comparisons matched, then the last argument (in this case the 8th argument) becomes the default value.

=SWITCH(C6, B9, AVERAGE(G5:G15), B10, COUNT(G5:G15), B11, SUM(G5:G15), 0)

And that is how the SWITCH function works in both Excel and Google Sheets.

Thank you for challenging yourself this week, and I hope you had fun and learned something new.

And, if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 2 Comments

  1. I had no idea that the SWITCH function was a thing! Is this new for Excel/Sheets? I feel like if I get more comfortable with this function, it could be super useful!

    1. It’s a little new, but it’s also been around for a while. And you are right for sure! Getting more comfortable with this function could open up a lot of opportunities!

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