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

 

[JEC]

You can not use dynamic array formula's in structured tables. You can drag this down

=[@Sales]+IFERROR(--F2,0)

 

[Fluff]

Another option

=SUM(INDEX([sales],1):[@Sales])

 

[Fowmy]

You can not use dynamic array formula's in structured tables. You can drag this down

=[@Sales]+IFERROR(--F2,0)

I think that's big limitation

Your formula works but I am waiting to see more replies.
I modified your formula as =[@Sales]+IFERROR(--F1,0)

 

[JEC]

Yes it should be F1, and it is a limitation for sure.

 

[Fowmy]

Another option

=SUM(INDEX([sales],1):[@Sales])

Yes, that too works. I wrote a blog back in January 2015 with this solution for running total in table : Calculate a Running Balance

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

I think that's big limitation

I disagree. One of the features of a table is that you put a formula in the top cell of a column and it fills the table column with that formula. A dynamic array formula may use/require less or more rows than exist in the table. To me that doesn't fit with the workings of a table.

Related to the above, as a general rule I would recommend the formula from post 3 over the formula from post 2 because the post 2 formula does not use/stick to the table structure nomenclature. To demonstrate, here is a small table with both formulas and both returning correct running totals.

Fowmy.xlsm
	D	E	F
1	Sales	RT	RT2
2	6	6	6
3	5	11	11
4	2	13	13
5	3	16	16
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=[@Sales]+IFERROR(--E1,0)
F2:F5	F2	=SUM(INDEX([Sales],1):[@Sales])

Of course one of the features of a table structure is that table rows can be added or deleted. If I right-click, say, E3 and "Delete -> Table Rows" this is what I get. Col E now returns incorrect results.

Fowmy.xlsm
	D	E	F
1	Sales	RT	RT2
2	6	6	6
3	2	2	8
4	3	5	11
Sheet1

Cell Formulas
Range	Formula
E2,E4	E2	=[@Sales]+IFERROR(--E1,0)
F2:F4	F2	=SUM(INDEX([Sales],1):[@Sales])
E3	E3	=[@Sales]+IFERROR(--#REF!,0)

 

[Fowmy]

I figured it out, REDUCE can be used this way which allows me to customize the calculation within LAMBDA:

 

[Fluff]

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