Iterate on prior result to make an array

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,667
Office Version
  1. 365
Platform
  1. Windows
Can someone please help me?

I am trying to create an array formula that iterates on the prior result. Here, the formula I have copied downwards uses the prior row's result. Instead I want an array formula that spills the results from one cell.

And I would also really like the array to stop before it first hits zero (i.e., upon the first time it goes to a fraction, 0.3055 here).

number bases.xlsx
D
336
4
519345992
6537388.667
714927.4444
8414.638889
911.5
100.30555556
110
120
130
140
Sheet1
Cell Formulas
RangeFormula
D6:D14D6=TRUNC(D5)/$D$3
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
How about
Excel Formula:
=LET(s,SCAN(D5,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/D3)),FILTER(s,s<>0))
 
Upvote 0
Solution
How about
Excel Formula:
=LET(s,SCAN(D5,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/D3)),FILTER(s,s<>0))
Thanks for your prompt reply, Fluff!

I have so much trouble comprehending the LAMBDA functions that I can never seem to be able to write them.

In any case, this is what I'm up to: creating a single-cell formula to convert from decimal to any other Base number, which I have limited to 49 using some keyboard characters.

Like here, where I convert 19345992 (base 10) to BINGO (base 36). Any advice you may have that increases efficiency would be much appreciated. There is probably a much better way, but I am just a journeyman.

number bases.xlsx
NOPQRST
3from Base 1019345992result:BINGO
4to Base:36single cell:BINGO
5
6divisorsmodulus ratioinverted modulus ratiotranslation
7537388.66672411B
814927.444441618I
9ordinalcharacter414.63888892323N
100011.51816G
11110.3055555561124O
1222
1333
1444
1555
1666
1777
1888
1999
2010A
2111B
2212C
2313D
2414E
2515F
2616G
2717H
2818I
2919J
3020K
3121L
3222M
3323N
3424O
3525P
3626Q
3727R
3828S
3929T
4030U
4131V
4232W
4333X
4434Y
4535Z
4636~
4737!
4838@
4939#
5040$
5141%
5242^
5343&
5444*
5545(
5646)
5747-
5848=
Sheet1
Cell Formulas
RangeFormula
T3T3=TEXTJOIN(,,T7#)
T4T4=TEXTJOIN(,,XLOOKUP(SORTBY(ROUND(MOD(LET(s,SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),FILTER(s,s<>0)),1)*$Q$4,0),SEQUENCE(COUNT(ROUND(MOD(LET(s,SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),FILTER(s,s<>0)),1)*$Q$4,0)),,COUNT(ROUND(MOD(LET(s,SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),FILTER(s,s<>0)),1)*$Q$4,0)),-1)),$N$10:$N$58,$O$10:$O$58,,1))
Q7:Q11Q7=LET(s,SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),FILTER(s,s<>0))
R7:R11R7=ROUND(MOD(Q7#,1)*$Q$4,0)
S7:S11S7=SORTBY(R7#,SEQUENCE(COUNT(R7#),,COUNT(R7#),-1))
T7:T11T7=XLOOKUP(S7#,$N$10:$N$58,$O$10:$O$58,,1)
Dynamic array formulas.
 
Upvote 0
Have you looked at Excel's Base function.
I have not used that function.

Does the following help?

T202502a.xlsm
AB
1
219345992BINGO
3
3b
Cell Formulas
RangeFormula
B2B2=BASE(A2,36)
 
Upvote 0
Thanks, Dave.

Yes, I knew about that FUNCTION. I am going through an intellectual exercise to understand how base conversion algorithms work.
Also, I want more ordinals than just the 36 that function BASE handles.
 
Last edited:
Upvote 0
How about
Excel Formula:
=LET(s,SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),f,FILTER(s,s<>0),m,ROUND(MOD(f,1)*Q4,0),ss,SORTBY(m,SEQUENCE(ROWS(m),,ROWS(m),-1)),CONCAT(XLOOKUP(ss,N10:N58,O10:O58)))
 
Upvote 0
How about
Excel Formula:
=LET(s,SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),f,FILTER(s,s<>0),m,ROUND(MOD(f,1)*Q4,0),ss,SORTBY(m,SEQUENCE(ROWS(m),,ROWS(m),-1)),CONCAT(XLOOKUP(ss,N10:N58,O10:O58)))
Terrific. Thanks again, Fluff!
 
Upvote 0
Thinking about the formula can be shortened a bit like
Excel Formula:
=LET(s,ROUND(MOD(SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),1)*Q4,0),f,FILTER(s,s<>0),CONCAT(XLOOKUP(SORTBY(f,SEQUENCE(ROWS(f)),-1),N10:N58,O10:O58)))
 
Upvote 0
Thinking about the formula can be shortened a bit like
Excel Formula:
=LET(s,ROUND(MOD(SCAN(Q3,SEQUENCE(100),LAMBDA(a,b,TRUNC(a)/Q4)),1)*Q4,0),f,FILTER(s,s<>0),CONCAT(XLOOKUP(SORTBY(f,SEQUENCE(ROWS(f)),-1),N10:N58,O10:O58)))
I think maybe there is an problem here, Fluff. I tried converting 17735 from Base 10 to Base 2. Your first formula yielded
100010101000111
and the shortened one reported
1111111

In fact, any target Base below 10 that is chosen yields an incorrect value.
 
Upvote 0

Forum statistics

Threads
1,226,462
Messages
6,191,177
Members
453,644
Latest member
karlpravin

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