function in Google Sheets is handy when dealing with multiple criteria based on a single result.
3 parameters, with the first parameter being the value to evaluate, the second and third parameter representing a pair of combined cases and values. There’s also an optional final parameter that acts as the default result if no cases are satisfied.
Here is a simple demonstration of the SWITCH function:
As demonstrated above the
function in its first parameter contains the
function which takes 2 values: a reference to a date cell
and the type of result sought (with
being the default type if no value is entered and therefore 1 representing Sunday as 1, Monday as 2, etc).
Depending on the day of the week the
function then proceeds with evaluating each case statement. The first case statement handles what to do if the result is
and if this matches to return the value
. As the value of the
function will return
it continues to progress through each case statement until it matches.
With this example, all cases have a corresponding value representing an abbreviated day of the week. If I wanted to change the formula so that
I could change the formula to this instead:
As you can see from the above example with 18th December 2021 representing a weekend day, no cases are matched to the result of the first parameter and therefore the default (the final parameter) is returned.
Why Not Use
The main differences between the
function and the
are that the
function can evaluate multiple criteria, and it does not provide a default value if NO condition is met, therefore you would need to ensure your formula captures all possible scenarios.
The alternative formula using the
formula for the above example would be something like this:
As you can see from the above formula it’s very long considering the alternative
formula! As each condition needs to be evaluated it all would need to contain the
formula to determine the correct value. I changed the second parameter to 2 to make it easier to capture the weekends as Saturday and Sunday would evaluate to greater than 5.
function in comparison evaluates the same expression and only evaluates the result according to each case statement.
Can A Case Statement Be An Expression?
function is quite strict on the case statements inside and cannot contain an expression. The case parameter must match a value likely to be returned from the first parameter’s expression.
For example, the following function would produce an error:
Using an expression does not work and will just produce an
result as the case needs to match the result from the first parameter.
If you want to see another example of the
function check my other post where I reduced my formula code by 20%
function provides an easy way to provide a result based on a single expression. It can be more succinct than the corresponding
function, however, does have the drawback that the case statements cannot be expressions themselves and must match actual results.
Next, you might like to explore more about the