ASCAN

ASCAN(a,[d])
a
array
[d]
direction argument ; 0 or omitted scan by array, -1 by rows, 1 by clms
Array SCAN, 3 in 1 function, SCAN by row, by column, by array
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
ASCAN, Array SCAN, 3 in 1 function, SCAN by row, by column, by array. This is my take of tboulden's SCANBYROW/BYCOL.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.
Excel Formula: 
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNOPQRST
1d,omitted (by array)d,-1 (by rows)d,1 (by clms)
2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)
3123413610136101234
45678152128365111826681012
5910111245556678919304215182124
613141516911051201361327425828323640
7
8d,omitted (by array)d,-1 (by rows)d,1 (by clms)
9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)
10123413610136101234
115678152128365111826681012
12910111245556678919304215182124
1313141516911051201361327425828323640
14171819201531711902101735547445505560
15212223242312532763002143669066727884
16
17Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post
Cell Formulas
RangeFormula
F2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)
F3:I6F3=ASCAN(A3:D6)
K3:N6K3=ASCAN(A3:D6,-1)
P3:S6P3=ASCAN(A3:D6,1)
A10:D15A10=SEQUENCE(6,4)
F10:I15F10=ASCAN(A10#)
K10:N15K10=ASCAN(A10#,-1)
P10:S15P10=ASCAN(A10#,1)
Dynamic array formulas.
 
Upvote 0
Hi David,
Did you test it ? Your formula does not work.
ARRAYS.xlsx
ABCDEFGHIJK
1
22 columns running totals
3sample=ASCAN(B4:C10,1)
41204512045
52302335068
643083780151
7520451300196
8712892012285
9439562451341
10492372943378
11
12David's formula
13=MAP(TAKE(B4:C10,SEQUENCE(7,,1,1)),LAMBDA(x,SUM(INDEX(x,,1))))
14120
15350
16780
171300
182012
192451
202943
21
Sheet2
Cell Formulas
RangeFormula
E3,E13E3=FORMULATEXT(E4)
E4:F10E4=ASCAN(B4:C10,1)
E14:E20E14=MAP(TAKE(B4:C10,SEQUENCE(7,,1,1)),LAMBDA(x,SUM(INDEX(x,,1))))
Dynamic array formulas.
 
Hi Xlambda,

This is a partial formula, I mean, is it possible to reach a complete formula with the formula TAKE in your opinion?
And ,If we change to- index(x,,2) ,We will get the solution to the second column.

David
 
Hi Xlambda,

I have a solution,Thanks for the inspiration!

=LET(z,B4:C10,MAKEARRAY(ROWS(z),COLUMNS(z),LAMBDA(x,y,SUM(TAKE(CHOOSECOLS(z,y),x)))))

David
 
You're welcome! 😆 I've been there though. I have used expandable "ranges" with makearray and lots of other constructions designs long time ago. What I have learned is that these designs should be used only when there is NO other way possible.
Here is why. A simple example easy to check.
Your formula for z=SEQUENCE(5000,20) takes 3 minutes and 20 seconds to calculate, meanwhile =ASCAN(SEQUENCE(5000,20),1) is instant. ✌️😉
So, for SUM , ASCAN is most efficient, and for other scenarios that require versatility (to be able to apply any operations between current value and previous values, through an acumulator) , the other 2 constructions. ✌️
 
Another example of applying the golden rule in lambda "programming": use new lambda helper functions designs only when there is NO other way. (because of their iterative nature, tend to be "slow")
My take of lrobbo314 's REPEAT function without using MAKEARRAY => more efficient
AREPT(ar,v,[h]), Array REPeaT function (can handle blanks)
ar: array
v: vertical repeat factor
h: horizontal repeat factor
- if v or h omitted, 1 value is assigned
Excel Formula: 
=LAMBDA(ar, v, [h],
    LET(
        a, IF(ar = "", "", ar),
        r, ROWS(a),
        c, COLUMNS(a),
        l, c * MAX(h, 1),
        s, SEQUENCE(r * MAX(v, 1), l) - 1,
        INDEX(a, MOD(QUOTIENT(s, l), r) + 1, MOD(s, c) + 1)
    )
)
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1
2=AREPT("A",2,3)<=>=REPT(B3,SEQUENCE(2,3)^0)
3AAAAAAA
4AAAAAA
5v,3,h,omittedv,omitted,h,3v,3,h,3
6=AREPT(B7:D10,3,4)=AREPT(U7:U9,3)=AREPT(U7:U9,,3)=AREPT(U7:U9,3,3)
7Aa1Aa1Aa1Aa1Aa1aaaaaaaa
8Bb2Bb2Bb2Bb2Bb2bbbbbbbb
9Cc3Cc3Cc3Cc3Cc3cccccccc
10Dd4Dd4Dd4Dd4Dd4aaaa
11Aa1Aa1Aa1Aa1bbbb
12Bb2Bb2Bb2Bb2cccc
13Cc3Cc3Cc3Cc3aaaa
14Dd4Dd4Dd4Dd4bbbb
15Aa1Aa1Aa1Aa1cccc
16Bb2Bb2Bb2Bb2
17blankCc3Cc3Cc3Cc3v,3,h,omittedv,omitted,h,3
18empty stringDd4Dd4Dd4Dd4=AREPT(U19:W19,3)=AREPT(U19:W19,,3)
19abcabcabcabcabc
20→v,omitted,h,4abc
21=AREPT(B22:D25,,4)abcv,3,h,3
22Aa1Aa1Aa1Aa1Aa1=AREPT(U19:W19,3,3)
23Bb2Bb2Bb2Bb2Bb2abcabcabc
24C3C3C3C3C3abcabcabc
25Dd4Dd4Dd4Dd4Dd4abcabcabc
26
27v,3,h,omittedrepeat patterns
28=AREPT(B22:D25,3)=AREPT(SEQUENCE(3,4),4,3)
29Aa1123412341234
30Bb2567856785678
31C3910111291011129101112
32Dd4123412341234
33Aa1567856785678
34Bb2910111291011129101112
35C3123412341234
36Dd4567856785678
37Aa1910111291011129101112
38Bb2123412341234
39C3567856785678
40Dd4910111291011129101112
41
Sheet1
Cell Formulas
RangeFormula
G2,T28,G28,AD22,G21,AD18,Y18,AF6,W6,AA6,G6,L2G2=FORMULATEXT(G3)
G3:I4G3=AREPT("A",2,3)
L3:N4L3=REPT(B3,SEQUENCE(2,3)^0)
G7:R18G7=AREPT(B7:D10,3,4)
W7:W15W7=AREPT(U7:U9,3)
AA7:AC9AA7=AREPT(U7:U9,,3)
AF7:AH15AF7=AREPT(U7:U9,3,3)
Y19:AA21Y19=AREPT(U19:W19,3)
AD19:AL19AD19=AREPT(U19:W19,,3)
G22:R25G22=AREPT(B22:D25,,4)
AD23:AL25AD23=AREPT(U19:W19,3,3)
G29:I40G29=AREPT(B22:D25,3)
T29:AE40T29=AREPT(SEQUENCE(3,4),4,3)
Dynamic array formulas.
 
Hi,

I'm stuck on a problem, and if I can use an lambda function for this or not, which I've spent almost all day thinking about
But, essentially, I have a table that looks like this that shows yearly percentages

31/03/202331/03/202431/03/202531/03/2026
5%6%3%8%

And I am trying to show this (via an lambda of some sort), the percentages, repeated four times, by quarter, for a table that looks like this.

30/06/202230/09/202231/12/202231/03/202330/06/202330/09/202331/12/202331/03/2024etc.
5%5%5%5%6%6%6%6%

I'm quite certain this can be lambdified (the row with the values at least), possibly both, but wasn't able to figure this one out, over the past few hours.

I wonder if anyone has any ideas on how to do this, perhaps create a generic function that can do the same by month too, from years to quarters (repeated 4 times), but also from years to months (repeated 12 times). Or even, if the information is presented by quarter to begin with, from quarters to months (repeated 3 times)

I'm still a bit clueless.

st001
 
Will post Code this evening. It is on other laptop!
I believe it will boost, enhance a stronger sense of community, and powerful idea sharing on this forum.
Thank you for your patience. 🙏
:)
 
My first glimpse idea, an universal concept that works for any size intervals. (not only same size)
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1
2
33811152127
4abcdef
5
6=SEQUENCE(,MAX(B3:G3))
7123456789101112131415161718192021222324252627
8
9=XLOOKUP(B7#,B3:G3,B4:G4,,1)
10aaabbbbbcccddddeeeeeeffffff
11
12
Sheet4
Cell Formulas
RangeFormula
B6,B9B6=FORMULATEXT(B7)
B7:AB7B7=SEQUENCE(,MAX(B3:G3))
B10:AB10B10=XLOOKUP(B7#,B3:G3,B4:G4,,1)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top