RANDBETWEEN with ascending order

[khardu]

Hello dear excel users,


how can I use RANDBETWEEN to list the result in an ascending order.







Thanking you.




Regards,




KHARDU

 

[T. Valko]

I would have definitely thought ROWS and COLUMNS as being non-volatile. But correct me if I am wrong, regardless of the content of the cell, ROWS($A$1:A1) would return the row number right?

No, not the row number but the COUNT of rows being referenced.

I mean to say if in A1 to A5 we have volatile formulas such as the ones described in the link you posted, the Row [COUNT] number would still return correctly... no?

Yes

This said, Yes using the first cell in which the target formula is entered as anchor, will definitely make it easier to maintain.

Thanks again, learned something new.

It's all about KISS.

 

[T. Valko]

I have ever introduced and used

ROW()-ROW($C$2)+1

which is volatile because ROW() is volatile.

ROW() is not volatile.

It's easy enough to test.

Open a new file.
Enter this formula in cell B5: =ROW()
Save the file and close it.

Now, open the file. Don't do anything and after a few minutes close the file.

If ROW() was volatile then Excel would have asked you if you wanted to save changes.

What ROW() is, is it's vulnerable to new row insertions. If you have a formula that uses ROW() and the formula is entered in cell B5 then ROW() evaluates to 5. If you insert new rows at or before row 5 then your ROW() function now refers to a different row and returns a different number which may negatively effect your formula.

If you use ROWS(...), it's not vulnerable to this type of row insertion. So, as a general rule we can say that using ROWS(...) is more robust that using ROW().

 

[Marcol]

As a note to this, if you have the Analysis Tool Pack add-in installed you could consider using MRAND()
This will prevent duplication of random numbers in your result.
You also have the option of a static or volatile result.
e.g.
For a volatile result select A2:A31 and put this in the formula bar

=MRAND(30,0,2099)+1000

Confirm with Ctrl +Shift+Enter not just Enter
You shold now have 30 random numbers from 1000 to 3099 in A2:A31

For a static result use

=MRAND(30,0,2099,TRUE)+1000

Drag B2:C2 Down to Row #31

Excel Workbook
	A	B	C
1	Random Nos	Ascending	Descending
2	1043	1021	3034
3	2268	1043	2963
4	2922	1117	2922
5	2420	1128	2730
6	1356	1140	2704
7	1627	1356	2548
8	2730	1397	2508
9	2704	1490	2429
10	2508	1543	2420
11	1397	1578	2401
12	1737	1592	2351
13	1578	1627	2337
14	1898	1650	2268
15	1490	1737	2007
16	1911	1898	1911
17	1592	1911	1898
18	1021	2007	1737
19	2429	2268	1650
20	2351	2337	1627
21	2337	2351	1592
22	3034	2401	1578
23	2401	2420	1543
24	1128	2429	1490
25	1543	2508	1397
26	2548	2548	1356
27	1117	2704	1140
28	1650	2730	1128
29	1140	2922	1117
30	2007	2963	1043
31	2963	3034	1021
Sheet1

 

[Marcol]

A further note ...

Using MRAND() can also eliminate helper column(s)

e.g.
Select A2:A31
Enter this in the formula bar, with or without ,TRUE

=SMALL(MRAND(30,0,2099[COLOR=#ff0000],TRUE[/COLOR])+1000,ROW($A$1:$A$30))

Confirm with Ctrl +Shift+Enter not just Enter
You shold now have 30 random numbers from 1000 to 3099 in A2:A31

Similarly with LARGE()

 

[Aladin Akyurek]

Thanks Aladin, really very informative.

Although I'm sure it's not needed, ROW()-ROW($C$2)+1 and ROWS($C$2:C2) are both robust when used in a formula which is entered in C2. Cyril, I apologize for the repeat.

 

[T. Valko]

As a note to this, if you have the Analysis Tool Pack add-in installed you could consider using MRAND()

MRAND is a function that is part of the Morefunc add-in. It's not a native Excel function.

 

[T. Valko]

Although I'm sure it's not needed, ROW()-ROW($C$2)+1 and ROWS($C$2:C2) are both robust when used in a formula which is entered in C2. Cyril, I apologize for the repeat.

Kind of expensive using 2 functions plus 2 math operations:

ROW()-ROW($C$2)+1

More efficient to simply use:

ROWS($C$2:C2)

 

[cyrilbrd]

Cyril, I apologize for the repeat.

No need to apologize, precision is what we need and this site is a great tool for all (forum members and guest) to learn the correct syntax and the various alternatives.

Re Mrand, as underlined by Biff, it is not native to Excel (and not available on Mac's version either). This said it looks as well an interesting approach to consider given that the Analysis Tool Pack is indeed installed on your version of Excel. (yes Biff my syntax was off, it does indeed refer to the COUNT of rows...)

KHARDU posted a similar yet different problem on this link. My feeble attempt to come up with a solution raised another limiting factor, If you have the time please have a look as it may certainly gives us all the opportunity to learn from each other.