ARANDBETWEEN

ARANDBETWEEN(va,vb,[r],[c],[uq],[fm])
va
real number
vb
real number (order not important, va can be > vb)
[r]
rows of result array ; if omitted r=1
[c]
columns of result array; if omitted c=1
[uq]
unique argument, if omitted, results can have duplicates, if 1, no duplicates (only unique values)
[fm]
format argument; if omitted, no format; not omitted => TEXT(result array, fm)
Random Numbers. Functions. Nonrandomness of random numbers.
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
ARANDBETWEEN Returns an array of random integers between 2 numbers, with or without duplicates.
Excel Formula: 
=LAMBDA(va, vb, [r], [c], [uq], [fm],
    LET(
        w, MAX(1, r),
        l, MAX(1, c),
        n, w * l,
        x, FLOOR(MAX(va, vb), 1),
        m, CEILING(MIN(va, vb), 1),
        k, x - m + 1,
        s, SEQUENCE(k, , m),
        IF(
            AND(uq, k < n),
            NA(),
            LET(y, IF(uq, SORTBY(s, RANDARRAY(k)), RANDARRAY(n, , m, x, 1)), z, INDEX(y, SEQUENCE(w, l)), IF(fm = "", z, TEXT(z, fm)))
        )
    )
)
 
Upvote 0
ARNDBTW.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Similar results can be obtained using RANDARRAY, but RANDARRAY is not capable to deliver values with no duplicatesunq
2uq, omitted => duplicates possibledupstot values
3=ARANDBETWEEN(-5000,5000,20,20)=COUNTA(B4#)
44881-240620013787-4374-4802835-2039-4334480025931823-3858563-1010-4867-4283771968-1255400
53729-440-3333-445627-1248-3296-335408642631044168-16633082-4681-18074411-1482-1997-4793
6-11683028-2425-43382614-2912487619693130449835002476-2970-32392673-4788317553736254759count unique values
7198714311792914-1887-139-141-1932-1896-4073559-588-4387-4079-760-5784029-72649973259=ROWS(UNIQUE(TOCOL(B4#)))
8151217-480-3759-25434211-3285-3245-2644-3032-360589-28-4672-2190-33913916-33552946-1001391
9-453-1570-4498-3684-216439022668-45712880-2603-889-181636061360379245934383-26332842-4764
10-35644442-3788-30721803-4533-4136-3617-44204457989917117-3037-37954313784573-2648793
112697-4796132248463698-2259-2202238967-1989-4417-4867406322482720-2996-25994140-313-2777
121094-198-2943-251230583679-688385747220142397-127-3818-2870-4756-2660-5673221-1403196
134733178-2816251339474-3739267636145593334-3623-2652319925591437188315593064944
14269541374254-205745301773-4263-4842513226439233073751-883-18154450-26934355-31462820
1523511922-3032-3906648-4476811742-2710116939222449-9749972878-4242-3921-138-711-4928
162902-575-259523162439-1072-1395-3043-1962652-1402222575-4041963-3959889636-1540-1399
17-4240-3185-2541611310-1162-46156753342-651244-2720316-1287291448851134293514892349
182169-47932729-21153631-1791-3365-3845-198884547230948682800198021161450-5234844752
19-2771135-1024-1612-2448-34311238-1521533-30484866-16720455361591-2408-645-47189973763
20-1115-21792986-1887-2365-4547-2399-3847-3791-2947163580-1534-2592-28264030-9472051331-3598
21-522-3060978-313-642-3579-40-149813541864-47-5212443131072328-323-38931613332
22-17273994-46094309-252-3416-2952-4557-158943743826-354-7731939-2896605-1582-274227161418
232803-39471172-3116-36833237-4184212824-310-4766-831-4535-34471971-1600-36764844141-911
24
25uq,1 => no dupscount unique values
26=ARANDBETWEEN(-5000,5000,20,20,1)=ROWS(UNIQUE(TOCOL(B27#)))
27-2106-6351328-46552484-972-3307-4814002-1300-1040-35193549-3804-1973457-48782342972-3859400
286672584-26531165-7681899-4513-2860500-4780-2414-127-3137-2702-4719-2995-1710421018434832
2923861529120-40243034-4197126545673159-3948629-37731088406817514456456-29681150-2978
30-2994464826761729-1881-428225-2155-693520-32654719-3718-7084600-3704-1027-416342223915
312532674-9543257-3837-1884-1063-3999835-4708-1085-2045-934-4663-21683155-1093-1329-694-4433
32181940961746417048372670-8613782-2251341514091563-48174991-1292-36333389-1808-4549-4551
33-23642133742-3897-711-4783539-2511582494-2711-2442-17084499130937603931428-690-1249
34-1652721911-6433525-48961565-1142260434125691421452-1790-2726-944-395517882005-4665
35162-866-1758-57441084772-119-197032782234618-38604920-1748-2956-4271-38231084-21204639
363612223-4924192-1279469642171916-4439-15434097-672-12551615-4459-344240892629-1188940
37-3935-104229034695-4067-1934660-4071542464-3499-2715355-907837-515-4042-18924326-4912
38-288-2436-685-36362380-4138236036992288-26863752-3394-17-4340-437478113492230-2063-1915
39491639352444-4353-3472320492-1486-3352-2900-42893220-710-4697451-97137411894-2145-3903
40-682-15842471670-2920-4733-30638193513108-2396-5254258726-436625722641185-1887-1139
41-15014018-3419-47863002-11143464-103222798-3275-10903443400-3379235-4467257949224642
42129-1272-15223461-754-37072057-2806-3410-2867-1840-3290-47404905-3349-40772538-831-1637-801
432461-884703-3087-1117387542951360-1694-2495-4179-4619502-26152447-2471072-12293116-2016
443584-4162-1217-2506-28594413608-2454-1467-298731604789-9392855-38244107453516963763-4734
459382934-383-363249403351536-289813662845376147853862-1697-177476-1200614-4220616
46-47494588-4387-2717-324-4057-2266-511-3121-199334034963608-58245035672118-1913-4695-4122
47
Sheet5
Cell Formulas
RangeFormula
B3,W26,B26,W7,W3B3=FORMULATEXT(B4)
B4:U23B4=ARANDBETWEEN(-5000,5000,20,20)
W4W4=COUNTA(B4#)
W8W8=ROWS(UNIQUE(TOCOL(B4#)))
B27:U46B27=ARANDBETWEEN(-5000,5000,20,20,1)
W27W27=ROWS(UNIQUE(TOCOL(B27#)))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B27:U46Expression=SUM(--(B27=$B$27:$U$46))=1textNO
B27:U46Expression=SUM(--(B27=$B$4:$U$23))>1textNO
B4:U23Expression=SUM(--(B4=$B$4:$U$23))=1textNO
B4:U23Expression=SUM(--(B4=$B$4:$U$23))>1textNO
 
For random dates distribution, we can hardcode dates interval => function friendly with any regional date formats
ARNDBTW.xlsx
ABCDEFGHIJKL
1Random dates btw any other 2 dates : (15-jul-22: 31-dec-22)
2dupsno dups
3=ARANDBETWEEN("15-jul-22","31-dec-22",20,3,1,"dd-mmm-yy")
406-Dec-2206-Sep-2205-Oct-2221-Oct-2203-Nov-2217-Aug-22
503-Dec-2229-Jul-2224-Sep-2206-Nov-2210-Aug-2218-Jul-22
622-Sep-2219-Dec-2220-Oct-2204-Nov-2208-Oct-2228-Jul-22
724-Oct-2216-Oct-2201-Aug-2230-Jul-2228-Nov-2202-Nov-22
825-Aug-2221-Sep-2205-Aug-2224-Oct-2225-Nov-2221-Dec-22
917-Aug-2219-Jul-2208-Sep-2220-Dec-2229-Oct-2220-Aug-22
1017-Dec-2210-Aug-2206-Aug-2218-Dec-2215-Oct-2222-Aug-22
1106-Dec-2201-Dec-2204-Sep-2203-Dec-2214-Nov-2210-Dec-22
1229-Oct-2221-Jul-2219-Jul-2231-Oct-2206-Aug-2230-Dec-22
1315-Nov-2206-Oct-2213-Oct-2222-Jul-2216-Jul-2203-Aug-22
1422-Sep-2207-Oct-2229-Dec-2228-Oct-2201-Sep-2223-Jul-22
1511-Nov-2209-Nov-2205-Aug-2225-Oct-2219-Dec-2228-Aug-22
1616-Jul-2230-Nov-2228-Dec-2226-Jul-2212-Dec-2227-Nov-22
1715-Sep-2209-Dec-2205-Oct-2211-Aug-2202-Dec-2203-Oct-22
1802-Nov-2215-Aug-2217-Jul-2215-Nov-2201-Aug-2208-Aug-22
1903-Dec-2207-Oct-2217-Aug-2202-Sep-2211-Sep-2214-Oct-22
2008-Nov-2212-Nov-2219-Jul-2216-Aug-2217-Jul-2230-Sep-22
2104-Aug-2220-Aug-2219-Jul-2222-Dec-2222-Oct-2219-Aug-22
2222-Sep-2216-Dec-2211-Aug-2226-Nov-2207-Oct-2201-Oct-22
2304-Sep-2214-Aug-2227-Nov-2216-Dec-2216-Sep-2212-Oct-22
24=ARANDBETWEEN("15-jul-22","31-dec-22",20,3,,"dd-mmm-yy")
25
Sheet6
Cell Formulas
RangeFormula
F3F3=FORMULATEXT(F4)
B4:D23B4=ARANDBETWEEN("15-jul-22","31-dec-22",20,3,,"dd-mmm-yy")
F4:H23F4=ARANDBETWEEN("15-jul-22","31-dec-22",20,3,1,"dd-mmm-yy")
B24B24=FORMULATEXT(B4)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:H23Expression=SUM(--(F4=$F$4:$H$23))=1textNO
F4:H23Expression=SUM(--(F4=$F$4:$H$23))>1textNO
B4:D23Expression=SUM(--(B4=$B$4:$D$23))=1textNO
B4:D23Expression=SUM(--(B4=$B$4:$D$23))>1textNO
 
ARNDBTW.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Random nr. between 1 and 3000, dups and no dups , format "0000"
2
3=ARANDBETWEEN(3000,1,20,20,,"0000")
417691720277925000895052526101417124208851273298205610356037829541629267117140089
519472068232026490967199928230460278624920493259313220862132412631688293821000574
619420639229424442411107519701551024304820524144721562579138114832190140025761871
723402200121517260738216325702792024429320675127411520275296801872379073705961650
805521111249226760993283712660903059825280500016727730485051021201341065525291201
905332664279706532154200612440673042129301421007705570451116410161944250219480384
1018552599152210902683158901902025136211352768031518032298275509221767114724572622
1128062998203422772987061629402786298817681945170720442808098421791011209228032819
1220550887175403031684225609690386202807981668081512292643157205270202272702650510
1315030092159921212404013507691976252600790796019903972419293225300690249918122192
1406111087252028270293092207341868031011880389283106682811013123602756024117442974
1500720035205211882286108510280831294819791184109208621112293203771072173825091773
1616882894293502401647060508222275254407071060062312782725056316370701109121590383
1710651814129822320197189328282279239420901824009016012307185402221375186117002614
1817650890250628621334116113402446204304941886255010171058012802781659083718532927
1928541046198803552585005315020839011117952340225304741472290906720869134113491851
2028571329204029480117291323840894259103961623273919382967185106670863109006552977
2124880132218910482599270107441508220419412823228915432757274013271205025711230758
2204670119136406921582260413350292166329511860124015490395013002792619057311932697
2324320478059211742859198200422136017719751971227220220260236422860149157004540723
24
25
26=ARANDBETWEEN(3000,1,20,20,1,"0000")
2716160171263904971529109927741706069109501852036714201891033505421067136413212845
2811230009176310301833064106452599043628322049148116321031259801412236231425800172
2901952362152102862451147820622427181028390517286226292012181426721491214101731290
3020931470083317522239254409522972119016040117168415380617294916780291131909302658
3127282480069929590671275729261046283317461261159518090654271817351140153923240055
3217270050025518471443195902821142067426912661023719200618035513770771291726051301
3329951639123923561774258720061980241327162088275902500465097524121773091528951016
3417892678267126130166095712042377220219662801288629270830245725951786098500241589
3514220063073401792434293008221651125025261285199019142922290604562821041709870008
3615411336289215430924284305220940000200421489101312241840147409132203147327942825
3718880394255808680101161709350262291108592406120815201418226609171650286920250395
3816812729044807260340284215462024009303122309286323612297159620831318049808752453
3922720912265307322506151217880887144914522070167516292654229606091488058703821823
4022491179244713972936210205270420227917120097133410770385020615562523266804472547
4115530491144011761332227311112748238105900039010404751801226307791867285722480374
4218560142070526860337021104840270258808990083186909441829186226850287134424200123
4318860439040520601032033809432384185025841593250120920358220104830902133105490885
4421521052083712442745281219990599073723401570192627230214036017241428299005510709
4513730893188014111636177624182322256316272950227802312602250013762284218021850159
4616542474070008382920155820201647101508161844047305290789220807292860219800492482
47
Sheet7
Cell Formulas
RangeFormula
B3,B26B3=FORMULATEXT(B4)
B4:U23B4=ARANDBETWEEN(3000,1,20,20,,"0000")
B27:U46B27=ARANDBETWEEN(3000,1,20,20,1,"0000")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B27:U46Expression=SUM(--(B27=$B$27:$U$46))=1textNO
B27:U46Expression=SUM(--(B27=$B$4:$U$23))>1textNO
B4:U23Expression=SUM(--(B4=$B$4:$U$23))=1textNO
B4:U23Expression=SUM(--(B4=$B$4:$U$23))>1textNO
 
Pls check this out, Mike's ExcelIsFun latest YT
Apparently has nothing to do with this thread but in fact it has a lot.
It's about "freezing" volatile functions to recalculate, including the random ones, and, about
the simplest lambda ever. My post at the comments :
"Other solution is to always use NOW() when you register an event. Shifts in hospitals or military can take more than 1 day.
You will say , yes but NOW() recalculates. No problem, we can make is stop .
The simplest lambda in history 😉
NW(cl) cl: cell's reference where we want to register the event, the very cell where we call the function.
Excel Formula: 
=LAMBDA(cl,IF(INDIRECT(cl)=0,NOW(),INDIRECT(cl)))

- call: =NW("c8")
If you do not edit the cell anymore it will not recalculate.
Used this concept before when I needed random array samples to "freeze". Can be done with any function that recalculates.
Note: Since is a circular reference we need this checked: File->Options->Formulas->Enable iterative calculation "
Obs: Moving the function or any reference change caused by inserting, deleting rows above or to the left, can cause REF err, or recalculating because hardcoded reference will not be acurate anymore.
No editing the cell, will never recalculate, always the function should be at "cl" address
NW is inspired from this one, that works with any function and has an extra argument that can trigger the function to recalculate or not.
CL(fn,cl,[k]) CaLculate function
fn: volatile function
cl: cell's reference where we write the function (circular reference)
[k]: if omitted, no recalculate ,1 can recalculate
Excel Formula: 
=LAMBDA(fn,cl,[k],IF(INDIRECT(cl&"#")=0,fn,IF(k,fn,INDIRECT(cl&"#"))))
More super fun cool stuff about random still pending to post.
Book1
ABCDEFGHIJKLM
1k, omittedk,1 can recalculate
2no recalcunique 1to10 random
3Start TimeEnd timeHoursHH:mm=CL(RANDARRAY(10),"g4")=CL(SORTBY(SEQUENCE(10),RANDARRAY(10)),"i4",1)
411-07-23 20:1513-07-23 01:4929.57702529:340.86164910
512-07-23 8:4513-07-23 02:0017.256611117:150.0808842
612-07-23 16:3013-07-23 02:109.668997229:400.9044086
70.2802813
8=FORMULATEXT(C4:E6)0.0519255
9=NW("c4")=(C4-B4)*24=TEXT(D4/24,"[h]:mm")0.6864614
10=NW("c5")=(C5-B5)*24=TEXT(D5/24,"[h]:mm")0.5039457
11=NW("c6")=(C6-B6)*24=TEXT(D6/24,"[h]:mm")0.463358
120.1785869
130.4696331
14
15
Sheet1
Cell Formulas
RangeFormula
G3,I3,C8G3=FORMULATEXT(G4)
C4C4=NW("c4")
D4:D6D4=(C4-B4)*24
E4:E6E4=TEXT(D4/24,"[h]:mm")
C5C5=NW("c5")
C6C6=NW("c6")
G4:G13G4=CL(RANDARRAY(10),"g4")
I4:I13I4=CL(SORTBY(SEQUENCE(10),RANDARRAY(10)),"i4",1)
C9:E11C9=FORMULATEXT(C4:E6)
Dynamic array formulas.
 
Using CL(fn,cl,[k]) CaLculate function to monitor an event (first occurrence of an event) on our spreadsheets, like when a cell is no longer empty, or when a cell gets deleted, or when a dynamic array or a table reaches a certain dimension, or when a total excedes a value, anything can be written.
calculate.xlsx
ABCDEFGHIJK
1
2Monitor first time when a cell will receive a value
3=CL(IF(B4<>"",TEXT(NOW(),"dd-mmm h:mm"),0),"e4")
40 => - if 0 the event did not happen
5
6=CL(IF(B7<>"",TEXT(NOW(),"dd-mmm h:mm"),0),"e7")
7x15-Jul 11:11
8
9
10Monitoring when a table reached a certain dimension
11=CL(IF(ROWS(Tbl)>5,"rws >5 "&TEXT(NOW(),"mmm/dd hh:mm:ss"),0),"e12")
12Nr.IDrws >5 Jul/15 11:39:32
13134
14254Monitoring when the totals exced 280
15321=CL(IF(SUM(Tbl[ID])>280,TEXT(NOW(),"ddd hh:mm"),0),"e16")
16490Sat 11:53
17543
18638
19718
20
Sheet2
Cell Formulas
RangeFormula
E3,E15,E11,E6E3=FORMULATEXT(E4)
E4E4=CL(IF(B4<>"",TEXT(NOW(),"dd-mmm h:mm"),0),"e4")
E7E7=CL(IF(B7<>"",TEXT(NOW(),"dd-mmm h:mm"),0),"e7")
E12E12=CL(IF(ROWS(Tbl)>5,"rws >5 "&TEXT(NOW(),"mmm/dd hh:mm:ss"),0),"e12")
E16E16=CL(IF(SUM(Tbl[ID])>280,TEXT(NOW(),"ddd hh:mm"),0),"e16")
 
Dedicated function to monitor an event:
WHEN(fm,cl,[ft])
fm: formula or expression that triggers the event, designed to deliver true or false
cl: cell's reference where we write the function WHEN
ft: date format argument: if omitted no format
Excel Formula: 
=LAMBDA(fm,cl,[ft],IF(INDIRECT(cl)=0,IF(fm,IF(ft="",NOW(),TEXT(NOW(),ft)),0),INDIRECT(cl)))
Next post, how to secretly monitor areas of a spreadsheet.
calculate.xlsx
ABCDEFGHIJ
1
2Monitor first time when a cell will receive a value
3=WHEN(B4<>"","e4")
40 => - if 0 the event did not happen
5
6=WHEN(B7<>"","e7","mmm-dd hh:mm:ss")
7xJul-17 12:19:50
8
9
10Monitoring when a table reached a certain dimension
11=WHEN(ROWS(Tbl)>5,"e12","mmm/dd hh:mm:ss")
12Nr.IDJul/17 12:34:22
13134
14254Monitoring when the totals exced 280
15321=WHEN(SUM(Tbl[ID])>280,"e16")
1649045124.52839
17543
18638=TEXT(E16,"hh:mm:ss")
1971812:40:53
20
Sheet2
Cell Formulas
RangeFormula
E3,E18,E15,E11,E6E3=FORMULATEXT(E4)
E4E4=WHEN(B4<>"","e4")
E7E7=WHEN(B7<>"","e7","mmm-dd hh:mm:ss")
E12E12=WHEN(ROWS(Tbl)>5,"e12","mmm/dd hh:mm:ss")
E16E16=WHEN(SUM(Tbl[ID])>280,"e16")
E19E19=TEXT(E16,"hh:mm:ss")
 
If we need "event" functions to cover bigger ranges, we can eliminate the hardcoded "cl" argument with an explicit cell/range argument => formulas can be moved and inserting or deleting rows or columns anywhere will not afect them at all, we can use drag and drop, we can use them in tables.
=NW("a4") ------> =NW(A4)
Also, will see in future posts that timing how long will take a function or "slow" formula to calculate, precisely, is amazingly easy to accomplish, no VBA or add-inns..that themselves can slow down the entire workbook. So clean precise timing.
New functions, same arguments:
NW(cl) Now
Excel Formula: 
=LAMBDA(cl,LET(x,CELL("address",cl),IF(INDIRECT(x)=0,NOW(),INDIRECT(x))))
WHEN(fm,cl,[ft]) When
Excel Formula: 
=LAMBDA(fm,cl,[ft],LET(x,CELL("address",cl),IF(INDIRECT(x)=0,IF(fm,IF(ft="",NOW(),TEXT(NOW(),ft)),0),INDIRECT(x))))
CL(fn,cl,[k]) Calculate
Excel Formula: 
=LAMBDA(fn,cl,[k],LET(x,CELL("address",cl),IF(INDIRECT(x&"#")=0,fn,IF(k,fn,INDIRECT(x&"#")))))
Book1.xlsx
ABCDEFGHIJKLMNOPQRS
1"event" array
2Every member of a team has to deliver 4 reports.Steps to set the "event" array, before any delivery happened
3Easy method to monitor when the reports are delivered - select range J6:M11, in edit mode, top left cell write WHEN(C6="x",J6) and Ctrl+Enter
4"x" -> delivered - Or, write top left cell and drag and drop
5r1r2r3r4Every time an "x" is set, the event array saves the time=FORMULATEXT(J6:M11)
6Axx18 Jul 21:47:170018 Jul 21:05:36=WHEN(C6="x",J6)=WHEN(D6="x",K6)=WHEN(E6="x",L6)=WHEN(F6="x",M6)
7Bxx018 Jul 20:43:3819 Jul 00:37:190=WHEN(C7="x",J7)=WHEN(D7="x",K7)=WHEN(E7="x",L7)=WHEN(F7="x",M7)
8Cxx019 Jul 01:07:10018 Jul 22:09:52=WHEN(C8="x",J8)=WHEN(D8="x",K8)=WHEN(E8="x",L8)=WHEN(F8="x",M8)
9Dx18 Jul 21:33:07000=WHEN(C9="x",J9)=WHEN(D9="x",K9)=WHEN(E9="x",L9)=WHEN(F9="x",M9)
10Ex0018 Jul 20:57:010=WHEN(C10="x",J10)=WHEN(D10="x",K10)=WHEN(E10="x",L10)=WHEN(F10="x",M10)
11Fxx018 Jul 22:16:34018 Jul 22:21:34=WHEN(C11="x",J11)=WHEN(D11="x",K11)=WHEN(E11="x",L11)=WHEN(F11="x",M11)
12
13Table referencesFormula in cell K17 dragged to the right
14=WHEN([@Prd]<>"",[@Date])=CL(RANDARRAY(20),K17#)=NW(O15)
15↓↓↓↓↓↓=CL(RANDARRAY(20),L17#)19 Jul 02:11:37
16Nr.DatePrdVal↓↓↓↓↓↓=CL(RANDARRAY(20),M17#)
17118 Jul 22:20:36A230.7516123220.8209051350.099333534
18218 Jul 22:28:46B320.2034283690.7328519880.72498428Every formula embeds volatile functions(NOW,RANDARRAY) but will never recalculate until
19318 Jul 22:31:30C190.6165316130.9350711830.822789363we reedit the cells that hold them.
200.2891399310.4550607090.132970167Reediting is good for resetting the event occurrence to the next one.
21↑↑↑0.2336487120.624220.313416777 - Selecting the cell and chosing different format will not trigger any cell to recalculate,
22=F18+([@Date]<>"")0.0024555690.1929117630.815459361only clicking inside the cell will trigger a new recalculated value
230.782174930.320269750.961601335 => pasting formats over these cells will not change the value.O25
24New product? Fill the 0.5829515640.5691534740.536870257
25with new data and first 2 clms will recalculate 0.0936940670.8647745450.784556657 - ready to post now:
26and save unique time0.7557411680.7414515150.89652849719 Jul 03:09:50
270.2137676640.7677455150.218469754😊
280.2334300880.2200126130.368510796
290.5862307050.8104310580.341135496
300.2358853910.2784833620.29013833
310.9432506040.4353832980.053058164
320.4692715120.8323769480.611408029
330.4425819540.5513046880.720071244
340.988075830.2349812930.742624069
350.23952710.0177133580.748598184
360.7234465860.7917115290.016755335
37
Sheet1
Cell Formulas
RangeFormula
O5,M16,O14O5=FORMULATEXT(O6)
J6:M11J6=WHEN(C6="x",J6)
O6:R11O6=FORMULATEXT(J6:M11)
G14,K14G14=FORMULATEXT(G17)
L15L15=FORMULATEXT(L17)
O15,O26O15=NW(O15)
G17:G19G17=WHEN([@Prd]<>"",[@Date])
K17:M36K17=CL(RANDARRAY(20),K17#)
F18:F19F18=F17+([@Date]<>"")
F22F22=FORMULATEXT(F19)
Dynamic array formulas.
 
Check out latest ExcelIsFun Mike's latest YT
Came up with an idea to make the concept fully dynamic and fast
RNDCOL(n,m) n: nr of rows, m: min/max/digits array
Excel Formula: 
=LAMBDA(n, m,
    LET(
        z, INDEX(m, 3, ),
        x, INDEX(m, 1, ) - NOT(z),
        ROUNDUP(RANDARRAY(n, COLUMNS(m)) * (INDEX(m, 2, ) - x) + x, z)
    )
)
EMT1857-1858.xlsx
ABCDEFGHIJKLMNOPQ
1n1M rows
2min/max/digits array
3min2357341000250003743264132765
4max37587742000270005000999150000
5digits0200310
6
7=RNDCOL(1000000,B3:H5)checking min/max interval values representation
8346623.4341539252284611.098345.1143957=BYCOL(B8#,MIN)
974790.141612250914050.621651.314972523573.0141000250003743264.1132765
10173804.4541647262423761.665441.4141788=BYCOL(B8#,MAX)
11324655.4341607266854799.354808.913557637587742000270005000999150000
12375808.5541737268604848.085922.3146330
13332740.5541065257264048.811679.3140875
1479869.7241978251984029.585668.3148568
15200778.1841499267624463.675481.3147388
16239592.9141838262563912.386725.9134339
17101713.9641711262134181.897811.2137642
18323584.0941921267124791.597378.7143270
19200649.5641579263674116.223392.3145790
20304633.2641106262014719.563909135746
21236837.6841715253573913.753817.8136450
22328670.9541923257623952.044278133624
23347732.5241081260814421.475894146201
24220652.1741270269154248.853886.6146553
25255585.1541050264504065.9966.3145146
2623778.2141072260014676.531875.3135139
2749861.3541418263524632.569278.2146997
28295629.0741010260584460.322464.2146885
29268760.7741568259414684.348948.3140075
30213763.8841355262284640.185869144604
31246751.0841667250044040.673493.8140263
32356709.0541235252134064.833410.1148651
33174713.6341059268784787.702716.6145469
3462817.8341818266514427.34386.6148829
35103638.7341277260884586.507893.6137113
36202657.8441472268444415.867850.6143084
37216860.1841330252964573.456778136195
38273828.3541762260814962.127834.7147920
3978780.1541033259934174.613437.1137157
Sheet1
Cell Formulas
RangeFormula
B7,J10,J8B7=FORMULATEXT(B8)
B8:H1000007B8=RNDCOL(1000000,B3:H5)
J9:P9J9=BYCOL(B8#,MIN)
J11:P11J11=BYCOL(B8#,MAX)
Dynamic array formulas.
 
Instead of copy pasting as values a volatile formula or function we can use:
FREEZE(□,ƒ) One time calculation of a formula or function ƒ that is or has volatile components. Does not recalculate. The only way to make it recalculate is dbl click in the cell for edit mode and hit enter.
□: cell address: the very cell where the function resides. (to avoid circular reference message File→Options→Formulas→ check Enable iterative calculation)
ƒ: formula or function
Excel Formula: 
=LAMBDA(□,ƒ,IF(□=0,ƒ,□#))
EMT1857-1858.xlsx
ABCDEFGHIJ
1Mike's example
201-01-25500Note: to avoid circular reference message
331-12-263750File→Options→Formulas→ check Enable iterative calculation
402
5instead of copy paste as values we can use FREEZE
6=RNDCOL(20,B2:C4)=FREEZE(E7,RNDCOL(20,B2:C4))
703-02-252990.6415-09-252983.48
822-08-25643.510-01-252691.79
919-01-263599.7506-04-251938.82
1011-12-26649.2408-11-253403.69
1109-09-25558.0807-12-25980.31
1206-07-252696.7531-05-263317.75
1328-02-262359.6802-10-252738.22
1410-06-253016.5318-05-253712.86
1516-01-262879.7525-02-252585.44
1620-02-251096.631-10-252559.7
1710-06-253339.0510-10-262548.51
1823-01-263093.8709-02-262803.61
1927-07-251169.2519-06-263009.33
2026-09-252416.8505-07-261059.62
2117-01-25804.6909-06-252589.27
2203-11-263099.7105-05-262237.79
2324-06-251057.0904-06-26868.47
2430-03-261267.9815-08-261168.48
2521-11-252906.5109-03-25706.66
2617-01-262097.6614-02-262560.14
27
28recalculatesdoes not recalculate
29to recalculate dbl click to edit E7 and hit enter
30
Sheet2
Cell Formulas
RangeFormula
B3B3=EOMONTH(B2,23)
B6,E6B6=FORMULATEXT(B7)
B7:C26B7=RNDCOL(20,B2:C4)
E7:F26E7=FREEZE(E7,RNDCOL(20,B2:C4))
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,836
Messages
6,181,252
Members
453,028
Latest member
letswriteafairytale

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