1. Introduction to the SCAN Function
The SCAN function in Excel is used to perform cumulative calculations on a range of data. It applies a function cumulatively across an array, returning an array of results. This can be incredibly useful in various applications, such as creating running totals, tracking cumulative sales, or analyzing trends over time.
2. Parameters of the SCAN Function
Here is the syntax for using the
SCAN function.=SCAN ([initial_value], array, lambda(accumulator, value, body))[initial_value] : required Sets the starting value for the accumulator. array: required The array to be scanned. lambda: required The LAMBDA used to accumulate the array. LAMBDA requires three parameters: accumulator: required The value totaled up and returned as the final result.value: required The current value from the array.body: required The calculation applied to each element in the array.3. Example Scenario
Suppose you are tracking a small business's sales data over a week. You want to calculate the cumulative sales for each day to better understand the trends.
4. Getting Started with the SCAN Function
Type on your keyboard:
=SCAN(
5. Enter SCAN Function Parameters
Here we set the starting value for accumulation to
0, selecting C5:C9 as the array to accumulate, and preparing to define the accumulation Lambda.
6. Enter Lambda Parameters
Here we name the accumulated result as:
Note that
result, with the array element as: x, then set the accumulation formula as: result + x, and complete the corresponding parentheses. Note that
result and x are user-defined names and can be renamed as per personal preference.
7. Effect of the SCAN Function
After pressing Enter to save, you can see the effect of the
SCAN function, which successfully accumulates the current sales for each day.8. Other Accumulation Formulas
The Lambda operations here are not limited to addition; we can add more custom operations.
For example, here we can halve the daily sales before accumulating.
For example, here we can halve the daily sales before accumulating.
9. 🎉 Finish! 🎉
Author's Note: I hope you can feel the effort I put into these tutorials. I hope to create a series of very easy-to-understand Excel tutorials.If it is useful, help me share these tutorials, thank you!
Follow me: