SCAN Function not working in Excel Table

[Fowmy]

Hi

How to make the SCAN function work in an Excel table? I am trying to get the running total but not getting the expected results. This works with ranges.

Thanks
Fowmy

 

[Peter_SSs]

I figured it out, ..

Whilst you have used a 'new' function to achieve the running total you wanted, it is not quite the same thing that you were attempting with the SCAN function. If SCAN was successful then it it would have spilled all the results down the table from the top cell, just as it does for a range as you mentioned in post 1. REDUCE does not do that, even for a range - see below.

Here is a 'range' example to demonstrate. Each formula is entered in a single cell. Only SCAN produces a result for each row of the input range. The O16 formula also uses the whole range as input but only produces the final sum of the column. The P16 formula is the equivalent of what you have shown in post 9 and only produces a result for that single cell that it is entered in (unless copied down each row). It 'works' in your table for the same reason that an 'ordinary' formula does, the formula is automatically copied down the table column calculating a single result on each line, not a spilled result from the top cell.

Fowmy.xlsm
	M	N	O	P
16	2	2	17	2
17	3	5
18	6	11
19	5	16
20	1	17
Sheet1

Cell Formulas
Range	Formula
N16:N20	N16	=SCAN(0,M16:M20,LAMBDA(a,b,a+b))
O16	O16	=REDUCE(0,M16:M20,LAMBDA(a,b,a+b))
P16	P16	=REDUCE(0,M$16:M16,LAMBDA(a,b,a+b))
Dynamic array formulas.

Why use reduce, rather than just sum, I think it would be far less efficient.

Agreed. I just did a test. In col S below, 1000 rows with random numbers 1-99.
Col S with equivalent of post 6 formula: 0.013 seconds to recalculate the 1000 results
Col T with equivalent of post 9 formula: 0.345 seconds to recalculate the 1000 results. ie About 26 times slower.

Fowmy.xlsm
	S	T	U
1	63	63	63
2	59	122	122
3	37	159	159
4	42	201	201
5	63	264	264
6	77	341	341
7	14	355	355
8	66	421	421
9	51	472	472
10	61	533	533
11	6	539	539
12	27	566	566
Sheet1

Cell Formulas
Range	Formula
T1:T12	T1	=SUM(INDEX(S:S,1):S1)
U1:U12	U1	=REDUCE(0,INDEX(S:S,1):S1,LAMBDA(a,b,a+b))