Congrats, great video! in google spreadsheet, INDEX function doesn't work as array so the implementation would be: =LAMBDA(low,high,total,ARRAY_CONSTRAIN(SORT({SEQUENCE(high-low+1)+low-1,RANDARRAY(high-low+1)},2,1),total,1))(1,60,5)

@ExcelGoogleSheets

Жыл бұрын

Looks good! 👍

@ExcelGoogleSheets

Жыл бұрын

Google Sheets implementation of INDEX & SUMIFS functions is super annoying.

omg i love your videos :) Thank you

Many Thanks ..so great🌹🌹🌹

Thanks for the lovely video

Efing marvelous

🎉 thank

Hi I was wondering if you could help me with generating 28 players in foursomes playing 7 weeks and each player in each group will not meet any of the players that he played each week throughout the seven weeks. I did it kinda manually and got through for 5 weeks and during the last two weeks there was instances that one player will meet once or twice during the seven weeks I just was wondering if there was a formula basically we looking on 7 columns and 7 Rows and place 4 players in each group Thanks in advance for any help

Wouldn't it be easier to use the third argument in the SEQUENCE function to generate from desired number. It would look like this SEQUENCE(high-low+1,1,low)

@ExcelGoogleSheets

Жыл бұрын

That would have been better 👍

@eddieabbott370

Жыл бұрын

​@@ExcelGoogleSheets Can you pick the pick 4 Lottery for WashingtonDC

This is great! I was curious, is there a way to create two columns filled with Random values using the Randunique function created (randomized list of 1-15 for example in two separate columns) without repeats on the same row?

Great video, pretty much what I needed instead I wanted the unique random number in a row not in a column. I had to adjust the formula not to be in columns but in rows. Took me a long time to figure it out but if anyone wants this formula in rows here it is. =LAMBDA(low,high,limit,INDEX(SORT(VSTACK(SEQUENCE(,high-low+1),RANDARRAY(,high-low+1)),2,,TRUE),,SEQUENCE(,limit))+low-1)

Nice

Is there any way, after generating the numbers, somewhere in the sheet to have them ascending?

Hi, Thanks for the lovely video. As things are going to the cloud recently, I have started to move my excel files to Google Drive. I have been using a small software that I had made in Libreoffice Calc to generate my business invoices. In my business, I have custom formatting to indicate currency. For instance, if I issue the invoice in USD, all the costs or prices are shown with USD sign. Likewise, if I choose Euro, everything is shown with Euro sign. To do that, I made two separate styles and apply these styles thanks to the conditional formatting. Unfortunately, in Google Sheets, it is not that simple. In fact, I couldn't find it. Do you know any workaround for this? Thanks for your help in advance and wish you a nice day.

Good use of LAMBDA. Too many creatiors make complicated (that's ok), often inefficient (not ok), SINGLE USE(???) LAMBDAs! BTW I use Advanced Formula Environment to take care of naming etc. Good video!.

@ExcelGoogleSheets

Жыл бұрын

What is "Advanced Formula Environment"?

@richardhay645

Жыл бұрын

I tried twice to answer but my longer comment keeps getting taken down! (Twice!) In (very) short it is an Add in from MS that is a more robust version of Name Manages that makes much easier to create LAMBDAs. Leila Gharani has the best video (made about 6 mo ago).

@ExcelGoogleSheets

Жыл бұрын

That makes sense. I pretty much avoid all addins.

@richardhay645

Жыл бұрын

@@ExcelGoogleSheets Since my third attempt to answer is still OK. I will add a bit more. All named fuctions from Nane Manager are available in this feature. The Add In ia available by going to add ins on the ribbon and searching for it. When you click add it will appear on the over-crowded! right side of the Home tab. You can build your core function inside the dialog box, bu I find it a bit easier to build it on the spreadsheet and paste it in the dialog box. You then convert it to a LAMBDA aftervyou paste it and add parameters, give it a name,etc. in the dialog box. Much more user friendly than Name Manager. I think Microsoft made it an add in insteD of an installed festure only because most users do not (yet) create LAMBDAs.

@richardhay645

Жыл бұрын

@@ExcelGoogleSheets It seems to be a stable add in and was created by Microsoft. I also generally avoid add ins. Watch Leila's video. That's what convinced me to use it. I think it will make it to the ribbon at some point.

Great ways, but i have question Is possible to make random numbers not change when update other cells (without macro or script) ?

need help how can I use this formula but to generate random numbers from least to greatest?

Great and smart. The biggest cons - it wouldn't work with Google Sheets. Would You mind to take am analog video in Sheets? Thanks on advanced

@ExcelGoogleSheets

Жыл бұрын

Will do when I have time.

@davidfernandezbajo

Жыл бұрын

=LAMBDA(low,high,total,ARRAY_CONSTRAIN(SORT({SEQUENCE(high-low+1)+low-1,RANDARRAY(high-low+1)},2,1),total,1))(1,60,5)

thanks for pointing out this video to me. But how would you do that with a range of text/values instead of just numbers?

@ExcelGoogleSheets

Жыл бұрын

use this combined with INDEX function to convert numbers to text.

@sergheyzaporojanu6872

Жыл бұрын

@@ExcelGoogleSheets nice idea thanks! how would it be in Google Sheets?

Which formula use for swap number as below example: 123 to 132, 213, 231, 321, 312 help please

Great video, but how can I use that without HSTACK and LAMBDA? How can I do the similar thing but using only the LET function? I don't have HSTACK and LAMBDA at the moment.

@ExcelGoogleSheets

Жыл бұрын

you should be able use CHOOSE function. instead of HSTACK(array1,array2) use CHOOSE({1,2},array1,array2)

@mungunbayar.bat-ochir

11 ай бұрын

=LET( min;B2; max;B3; size;B4; population;max-min+1; is_valid;AND(max>=min+1;population>=size); result;IF(is_valid;TAKE(SORTBY(SEQUENCE(population;;min);RANDARRAY(population));size);"invalid input"); result )

How could you create a single unique random number such as the random number that Microsoft Access Creates to use as unique ID,s

9:23 to cont...

can you use take instead of sequence

@ExcelGoogleSheets

Жыл бұрын

Instead of INDEX you mean? Yes, you could.

Since when did you start making excel videos?

@ExcelGoogleSheets

Жыл бұрын

Been making them before I even knew what Google Sheets was.