Google Sheets offers a simple way to create segments from your data by using functions such as IF and IFS, that evaluate logical expressions and return different values depending on whether or not the conditions are true or false.
For example, imagine we would like to categorize transactions in a table as being small or large, with values of $100 or more as large, and values < $100 as small. The IF function in Google Sheets has the following syntax:
IF(condition to be evaluated, value if true, value if false)
Taking the Payments tab as an example, here's what that looks like in Google Sheets:
By typing the first part of the function, Google Sheets provides us additional information on the syntax it expects from the IF function. In our case, the logical expression to evaluate is if the Payment Amount in column E is greater or equal to $100 or not. In the case where we evaluate if the amount is greater or equal to $100, the function would look like:
=IF(E3 >= 100, "Large", "Small")
We could also choose to write the function to compare if values are less than $100, which would look like:
=IF(E3 < 100, "Small", "Large")
Both these functions return the same result. In Google Sheets, we can fill in this function in cell G3, and then populate the rest of the relevant cells in column G with the same equation:
What happens if we need to segment our data into more than just one category? The IF function allows us to evaluate one condition only, and then outputs a value if that condition is true and a different value if that condition is false. This is where the IFS functions comes into play.
For example, imagine we would like to categorize the foreign exchange rate for US to Canadian dollars into small, medium and high rates. Let's assume that rates < 1.3 are small, rates between 1.3 and 1.4 are medium, and rates > 1.4 are high. The syntax for IFS in Google Sheets looks like this:
Following this syntax, the exchange rate formula would be:
=IFS(ROUND(D3, 1) < 1.3, "Small", ROUND(D3, 1) = 1.3, "Medium", ROUND(D3, 1), "Large")
Here we introduce a new function as well, ROUND, that rounds a number to a specified number of decimal places (1 in our case). We will introduce other mathematical functions in future lessons.
Nesting with IF and IFS functions
One thing to note is that sometimes, segmenting our data won't be as clean as using the IF functions to list different conditions to evaluate, but will be a combination of conditions. In these cases, the IF functions can be nested with other functions such as AND & OR, to produce a variety of different logical conditions. See the Q&A article in this section for some detailed examples.