LiveFlow raises $13.5 million Series A led by New York-based Valar Ventures and launches LiveFlow Next to revolutionize accounting!
Read More
Arrow
Back to guides
Google Sheets Formulas

How to Use SCAN Function in Google Sheets

In this article, you will learn the SCAN formula and how it works in Google Sheets.

This function is meant to be used with the LAMBDA function. The SCAN formula processes each value in a selected range or array as input values for the LAMBDA function in an accumulated way.

The SCAN formula returns each application result (intermediate result) in cells, and you will see the total accumulated number in the last cell in the array. The output size is the same as the chosen area for the input values.

If all assumptions are the same, the SCAN and REDUCE functions return the same total cumulative result. The difference is that the SCAN function shows all intermediate results leading to the final cumulative number, whereas the REDUCE function presents only the last cumulative number in a cell. 

How to insert the SCAN formula in Google Sheets

  • Type “=SCAN” or go to “Insert”“Function”“Array”“SCAN”.

  • Input the initial value for an accumulator if needed.

  • Select an array or a range. Each value in the selected area is used as an input value for the LAMBDA formula.

  • Enter a LAMBDA function with placeholders and logic.

  • Press the “Enter” key.

How to insert the SCAN function in Google Sheets

Why spend hours creating spreadsheets, when we have already done the work for you?
100+ financial spreadsheet templates, all free and ready for you now.
Free Spreadsheet Templates

What is the SCAN function in Google Sheets?

The SCAN function is beneficial when you want to process a series of input values through a formula, calculate and show intermediate total cumulative numbers for each process of an input value and the final cumulative number.

Also, you can show all the results by inserting the SCAN formula in a cell.

The generic formula is as follows:

=SCAN(initial_value, array_or_range, lambda)

Initial_value: This determines the initial value the accumulator has. If this is input, the formula starts calculations considering the initial value.

Array_or_range: Each value in the selected field is used as an input value in the LAMBDA function.

Lambda: Input the LAMBDA function such as “LAMBDA(CurrentCumulative, InputValue, CurrentCumulative+InputValue)”. You need to define two parameters such as “CurrentCumulative” and “InputValue”, and “formula_expression”

The first placeholder corresponds to the current value in the accumulator. The second parameter resolves to the current value in “array_or_range” (each input value).

Also, ensure you secure enough space for this formula because it spreads outcomes to the specified size. You can also use your Named Functions here.

 

Without examples, this formula is hard to understand.

Assume you are a finance manager. You need to calculate and show your company’s cash position at the end of each much for the fiscal year ending December 2021. 

The SCAN function helps you to show the cumulative cash position at each end of the month, considering the cash position at the end of the last year and the cash flow in each month. For instance, the Cash position of 1,900 in Mar. 2021 is the sum of the cash position of 1,400 in Feb 2021 and the monthly cashflow of 500 in Mar.2021.

How to use the SCAN function in Google Sheets with an example

The assumptions in the picture above are as follows:

Initial_value: 1,000

Array_or_range: C41:C52

Lambda: The first parameter, “CurrentCumulative”, resolves to the current value in the accumulator, meaning the total accumulated value by the logic up to the previous steps. Another parameter, “InputValue”, evaluates to an input value.

Learn how LiveFlow can save you hours a month on financial reporting!
Just 30 minutes can change the way your business operates forever.
Book a Demo

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Cta Photo

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how!

Get personal help

We guarantee you personal help on chat or Zoom within maximum 6 hours between 9am and 10pm EST.
Blue Tick
Email us at: [email protected]

Liked this article? Then you'll love the ones below

Supercharge your financial reporting today

See LiveFlow in action and discover how to streamline your workflows.