Generate Random Data Sets with Dates & Sales Numbers: RANDARRAY Function. Excel Magic Trick 1857
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1857-...
Learn how to create a data set with dates and sales numbers using an Excel Formula.
Topics:
1. (00:00) Create Inputs for Randon Data Set
2. (00:37) Create Random Dates using RANDARRAY function
3. (01:21) Create Random Sales amounts using RANDARRAY & ROUND function
4. (02:00) Paste Special Values Trick!
5. (03:00) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #randomdata
Excel Lambda is having problems with RUclips Comments. Here is the post he wanted to make:
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)
)
)
Mr Excel Message Board shows full commnet:
www.mrexcel.com/board/threads/arandbetween.1220480/#post-6164609
Go Team!!!!!
YT can not bend us !! Together we are strong 😊✌🙏🙏 Go Team !!
@@Excelambda Perfectly said!!!!
Mike, Monday is ExcelisFunday! Thanks for starting off the week!
Yes!!! The new slogan : Monday is ExcelisFunday! : ) : ) : ) : ) : ) : ) : ) : ) : ) : )
Thanks Mike!! :) :)
You are welcome, Formula Guy!!!
Another great video 📹 on my favorite excel channel :):):)
Nader : ) : ) : )
Thanks amazing Mike for this EXCELlent video.
You are welcome!!!!
I like your short format videos! RAND fuctions have definitely evolved for the better over the years!!
Yes, indeed, RAND evolution : ) :)
Thank you for this video! Use a lot 🙌👍
: ) : ) : )
That's cool
: ) : ) : ) : )
If we want to "freeze" something volatile I use FREEZE function
=LAMBDA(c,fm,IF(c=0,fm,c#))
where "c": the very cell where we need the formula ; "fm" the formula
Example: To freeze the single cell formula solution :
=ROUNDUP(RANDARRAY(P1,2)*HSTACK(P3-P2,P5-P4)+HSTACK(P2,P4),{0,2})
at A9 cell, we call:
=FREEZE(A9,ROUNDUP(RANDARRAY(P1,2)*HSTACK(P3-P2,P5-P4)+HSTACK(P2,P4),{0,2}))
The only way to make it recalculate is to click the cell in edit mode and hit enter. Even if is dragged and dropped still will not recalculate (cell ref. being relative will change to the new location)
Only for fun other 2 ways with MAP and REDUCE ( other than the simple nondynamic one: hstack(randarray1,randarray2) )
=MAP(IF(SEQUENCE(20),{1,2}),LAMBDA(x,INDEX(RANDARRAY(,,{"01-01-25",500},{"31-12-26",3750},{1,0}),x)))
=DROP(REDUCE(0,SEQUENCE(20),LAMBDA(v,i,VSTACK(v,RANDARRAY(,,{"01-01-25",500},{"31-12-26",3750},{1,0})))),1)
PS. Tried to post this before, under my first comment, but did not show
Yah, sometimes RUclips Comments does that. If it does, just post again.
I tried:
=FREEZE(A9,ROUNDUP(RANDARRAY(P1,2)*HSTACK(P3-P2,P5-P4)+HSTACK(P2,P4),{0,2}))
But got circular reference...
Any idea?
And, wow: This is a cool concept:
RANDARRAY(,,{"01-01-25",500},{"12-31-26",3750},{1,0})
Woo Hoo!!!!! : ) : ) Just beautiful.
I got it to work:
=MAP(IF(SEQUENCE(20),{1,2}),LAMBDA(x,INDEX(RANDARRAY(,,{"01-01-25",500},{"12-31-26",3750},{1,0}),x)))
To get two columns of 1,2, cool:
IF(SEQUENCE(20),{1,2})
Thanks for all the fun. I got them all posted in the download workbook for the Team : )
@@excelisfunto activate circular reference:
File→Options→Formulas→ check Enable iterative calculation 😉
Today I learnt that I can't copy and paste values when the range is inside table *with* header selected!
Hi thank you for this video!
I am new to channel and excel, where should I start as beginner please? I have already subscribed your channel😇
Home page has intro video and all my classes:
www.youtube.com/@excelisfun
Here is my beginner class:
ruclips.net/p/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
@@excelisfun thank you!
@@waseempervezx4460Have fun learning : )
Thanks Mike
I need trick help
Is there way to conditional format cell in table = " congration"
when i put search cell = " grat "
and use filter as search for this letters in this table
i want the this letters " grat " only colored or formated in the " congration"
I am not sure how to do that. You can try this great Excel question site; MREXCEL.COM/BOARD
@@excelisfun thanks
Hey Mke ... i have a question about groupby function ... the function part is not showing a list of functions ... it just show after i insert the first litter ?!!!
The function is in beta, but it has not been released to all of M 365. Microsoft says it will be soon, but they don't tell us exactly when : (
I am using excel for my work, I have too many rows in my file and I have to copy Formulas From top to bottom and it has to limit upto the last row, It should limit automatically while adjust rows and copy automatically while I add datas to reference Column. What to do?
Very nice Sir. But, how to get random names or countries etc.
=INDEX({"Country1,","Country2"},RandARRAY(50,,1,2,1) change the max anytime the countrys increase.
@@excelisfun Thank you Sir. I will check and reply.
You forget to give a mention to your trick to generate a random list of randomly assorted values,
INDEX({"A","B","C","D"}, RANDARRAY(number of rows required ,1,1,4,TRUE) )
, just to add you make using the right click drag thing look easy, I always end up copy / paste
in frustration with it. 🤔🤔
Ctrl-C & Shift-Ctrl-v to paste values (quite new to Microsoft products but been around with Google for a while).
I'd love to hear of a simple short cut to past formats only.
But still i love Mike's right click drag method.
You are totally right. I am glad you posted the word trick too : )
I added your example to the download : ) : )