Welcome to Excel Jet Consult blog post. In this Microsoft Excel video, I demo how to use the new SCAN LAMBDA Helper Function to calculate % of Grand Total. Like | Share | Subscribe
Video
SCAN Function
The SCAN Function scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
Syntax
=SCAN ([initial_value], array, lambda(accumulator, value))
The SCAN function syntax has the following arguments and parameters:
- [initial_value] Sets the starting value for the accumulator.
- array An array to be scanned.
- lambda A LAMBDA that is called to scan the array. The LAMBDA takes two parameters:
- accumulator The value totaled up and returned as the final result.
- value The calculation applied to each element in the array.
Function Application
In this example, we have a simple Sales Records formatted as an Excel Table as seen below
Our goal is to:
- Extract the unique years
- Use SUMIFS to calculate Sales Amount
- Use Scan Function to calculate Percentage of Grand Total
Execution.
- In cell J2, execute the formula: =SORT(UNIQUE(Data[Year]))
- In cell K2, execute the formula: =SUMIFS(Data[Sales Amount],Data[Year],J2)
- Click Enter and copy down the formula using fill handle
- In cell L2, execute the formula: =SCAN(K2,SUM($K$2:$K$22),LAMBDA(a,c,a/c))
- Click Enter and copy down the formula using fill handle
Cell K2 which is the first argument of the SCAN Functions, handles the starting value for the accumulator. Then, we used SUM function and absolute cell reference to aggregate the values in range K2 to K22. Thereafter, we nested LAMBDA function and used a and c as the accumulator and value respectively. Finally, we divided the accumulator by the value which gave us the % of GP as seen in the caption below
To see how to calculate percentage of grand total using PivotTable, kindly watch the video.
See you in the next tutorial