Reverse the SCAN() in Excel

SCAN and REDUCE are exciting new Excel functions that are useful for creating running totals, accumulating a mortgage balance, modeling the path of a rocket, approximating the solution to a differential equation, and so on. These functions are exciting because they implement a FOR loop on the spreadsheet without using VBA. However, these functions only work in one direction, right to left then top to bottom.

This article shows how to reverse the direction of SCAN and REDUCE so they scan bottom to top. Reversing SCAN and REDUCE is useful for tasks like discounting cash flows and solving differential equations with a right boundary condition.

What are SCAN and REDUCE?

SCAN and REDUCE calculate an array of values developed from the following recursive formula:

a[i] = function(a[i - 1], v[i])        

The array a is called the accumulator. SCAN and REDUCE cycle through the array v from left to right then top to bottom and recursively calculate the values of a. SCAN returns the entire array of accumulated values and REDUCE only returns the final accumulated value.

The Excel syntax is:

=SCAN([initial_value], v_array, LAMBDA(a, v, calculation))        

and

=REDUCE([initial_value], v_array, LAMBDA(a, v, calculation))        

The LAMBDA takes the prior value of a, and the current value of v, and combines them to make the next value of a that will be passed to the next iteration of the loop.

Reversing the SCAN

SCAN and REDUCE are great when we know the starting value and we want to step forward to find the ending value. But oftentimes, we know the ending value, and we want to step backwards to find the starting value. Instead of cycling through the control array top to bottom, we occasionally want to cycle from the bottom to the top.

In finance, we might want to discount future cash flows instead of accumulating past cash flows. In differential equations, the boundary condition might be on the right side of an interval instead of the left side.

Our goal is to reverse the flow of SCAN and REDUCE so they use the following recursion. Notice that the i-1 is changed to i+1.

a[i] = function(a[i + 1], v[i])        

RSCAN and RREDUCE

This section shows how to make LAMBDA functions that reverse SCAN and REDUCE. We give these new LAMBDA functions names, RSCAN and RREDUCE, so they can be used anywhere in the workbook. This is all accomplished without resorting to VBA. The new functions will use the same syntax as their parents.

To make the new functions, open the name manager (ctrl-F3) and add a new name, RSCAN. Entering a comment is optional, but the comment is useful because it will show up in the formula argument ToolTip when you type the function in a formula later on.

In the Refers to box, paste the following formula and press OK:

  
=LAMBDA(initial_value,array,function,
   LET(
      reverse, LAMBDA(array,
         LET(
            row, ROWS(array),
            col, COLUMNS(array),
            INDEX(array, SEQUENCE(row,1,row,-1), SEQUENCE(1,col,col,-1))
         )
      ),
      reverse(SCAN(initial_value, reverse(array), function))
   )
)        

Repeat the processes to add the RREDUCE function to the Name Manager. In the Refers to box, paste the following formula:

=LAMBDA(initial_value,array,function,
   LET(
      reverse, LAMBDA(array,
         LET(
            row, ROWS(array),
            col, COLUMNS(array),
            INDEX(array, SEQUENCE(row,1,row,-1), SEQUENCE(1,col,col,-1))
         )
      ),
      reverse(REDUCE(initial_value, reverse(array), function))
   )
)        

The above RSCAN and RREDUCE functions work by first reversing the array used in the scan. Then they use Excel's built-in SCAN or REDUCE. Finally, they reverse the array returned by SCAN or REDUCE.

Example 1: Concatenate Characters in an array

Enter the sample data into cells A1:C2, and then copy the formula into cell C4:

=RSCAN("", A1:C2, LAMBDA(a, b, a & b))        

Example 2: Discount cash flows

Enter the sample data into cells A1:A5, and then copy the formula into cell C2:

=RSCAN(,A2:A5, LAMBDA(pv_next, cash_flow, pv_next / 1.05 + cash_flow))        

Copy the formula into cell E2:

=RREDUCE(,A2:A5, LAMBDA(pv_next, cash_flow, pv_next / 1.05 + cash_flow))        

Discussion

SCAN and REDUCE implement a FOR loop of the form

a(0) = initial_value
FOR i FROM 1 TO n: a(i) = function(a(i - 1), v(i))        

This article showed how to reverse the loop so that it becomes a loop of the form

a(n + 1) = initial_value
FOR i FROM n TO 1: a(i) = function(a(i + 1), v(i))        

My plea for Microsoft is to add an additional optional parameter to SCAN and REDUCE after the LAMBDA parameter. The additional parameter will tell Excel the direction to cycle. The default will be 1, which is top to bottom, but -1 will cause SCAN and REDUCE to cycle bottom to top.

In the meantime, while Microsoft is ignoring my emails, use the hack described in this article to create RSCAN and RREDUCE.

Coming up Next

SCAN and REDUCE create FOR loops in the worksheet. This article showed how to tweak their behavior to expand their looping power. I plan to write additional articles that will show how to expand the looping power even more.

Upcoming topics:

• Pass multiple vectors to SCAN instead of only one vector.
• Pass multiple values from one SCAN iteration to the next.
• Reaching back more than one iteration with the SCAN function.