The SCAN function in Google Sheets is a powerful tool that allows you to apply a custom LAMBDA formula to each value in an array or range. With the SCAN function, you can easily get the running total of an array of values and even reset the total at specific thresholds or changes in the data. In this article, we will explore different examples to help you understand and master the SCAN function in Google Sheets.
Syntax of the SCAN Function in Google Sheets
Before we dive into the examples, let’s take a look at the syntax of the SCAN function:
SCAN(initial_value, array_or_range, LAMBDA)initial_value: The starting value of the accumulator.array_or_range: An array or range to be scanned.LAMBDA: A custom LAMBDA formula that takes two name arguments to scan the array or range.
The LAMBDA formula syntax is as follows:
=LAMBDA([name, ...], formula_expression)(function_call, ...)Here, name represents the current value in the accumulator, and formula_expression is the formula to be applied. The function_call is only required in standalone LAMBDA use.
Easy-to-Follow Examples of the SCAN Function
Now, let’s explore some simple examples to understand the usage of the SCAN function in Google Sheets.
Example 1: Joining Text
Suppose we want to join text values in a range together using the SCAN function. Here’s how the formula would look:
=SCAN("✻", A1:A3, LAMBDA(a,v,(a&v)))In this example, the initial_value is set to “✻”, and the array_or_range is A1:A3. The LAMBDA formula in this case is (lambda(a,v,(a&v))), where a represents the current value in the accumulator, and v represents the current value in the range. The accumulator a is updated in each step to the intermediate value obtained in the last/previous step.
Example 2: Running Total with Numbers
Now, let’s consider an example where we want to calculate the running total of a range containing numbers. The formula would look like this:
=SCAN(0, A11:A14, LAMBDA(a,v,(a+v)))Here, the initial_value is set to 0 since we are working with numbers. The array_or_range is A11:A14, and the LAMBDA formula (lambda(a,v,(a+v))) performs the addition operation on each value. Similar to the previous example, the accumulator a is updated in each step to the intermediate value obtained in the last/previous step.
Example 3: Resetting the Running Total at a Threshold
If we want to reset the running total when it reaches a certain threshold, we can modify the formula slightly. For example:
=SCAN(0, B2:B16, LAMBDA(a,v,(IF(a>=10,v,a+v))))In this example, the initial_value is still 0, but the LAMBDA formula (lambda(a,v,(IF(a>=10,v,a+v)))) includes an IF logical test. If the accumulator a reaches or exceeds 10, the value is reset to the current value v, otherwise it’s added to the accumulator. This allows us to reset the running total at a specific threshold.
Advanced Examples of the SCAN Function
Now that we have covered the basics, let’s explore some more advanced examples of the SCAN function in Google Sheets.
Example 1: Resetting the Running Total at Blank Cells
In some cases, we might want to reset the running total at specific points, such as when encountering a blank cell. Here’s an example formula for that scenario:
=SCAN(0, B2:B16, LAMBDA(a,v,(IF(v="",v,a+v))))In this formula, we use the same logic as before, but instead of checking against a threshold, we check if the current value v is a blank cell. If it is, the value v is used as the new accumulator, effectively resetting the running total.
Example 2: Performing Group-Wise Count
The SCAN function can also be used for more advanced calculations, such as performing group-wise counts. For example, if we have a range of country names and want to count the occurrences of each country, we can use the SCAN function in combination with other formulas:
=ArrayFormula(SCAN(0, UNIQUE(A1:A6), LAMBDA(a,v,(SUM((v=A1:A6)*1)))))In this example, we first use the UNIQUE formula to obtain a list of unique country names. We then use the SCAN function to iterate through this list and count the occurrences of each country using the LAMBDA formula (lambda(a,v,(SUM((v=A1:A6)*1)))). The result is a count of each country’s occurrence.
Conclusion
The SCAN function in Google Sheets is a powerful tool that allows you to perform custom calculations on arrays or ranges of data. Whether you want to calculate running totals, reset values at specific points, or perform more advanced calculations, the SCAN function can help you achieve your goals. By mastering the examples provided in this article, you’ll be well-equipped to leverage the full potential of the SCAN function in Google Sheets.
To learn more about Google Sheets and other useful tips and tricks, visit Crawlan.com, your go-to resource for all things Google Sheets.
