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

 

[cyrilbrd]

Yup, got it.
Thanks Biff, as well described by Aladin the formula does count the rows, just a way the get the 1st then 2nd then 3rd position ect (k).
Could have been ($A$1:A1) and it would have worked the same.

 

[T. Valko]

Could have been ($A$1:A1) and it would have worked the same.

Yes, that would evaluate the same. However, you should use the cell references where the formula(s) are being entered.

Let's assume you enter the formula in C2 and copy down but you use ROWS($A$1:A1) as the incrementer. If any of the cells in ROWS($A$1:A1) change then the formula(s) will recalculate. If you use ROWS(C$2:C2), those cells will only recalculate when other references in the bigger formula change.

 

[Aladin Akyurek]

Thank you, yes it indeed makes sense. Cool alternative.

You are welcome.

Yup, got it.
Thanks Biff, as well described by Aladin the formula does count the rows, just a way the get the 1st then 2nd then 3rd position ect (k).
Could have been ($A$1:A1) and it would have worked the same.

ROWS($A$1:A1) or any other range with just 1 row in it or even ROWS($X$1:X7)-6 which yields 1 will all do. The good practice is to use the first cell in which the target formula is entered as anchor, making it easier to maintain. More importantly, this practice also prevents us choosing inadvertently a range whose cells would possibly contain volatile formula(s) like =NOW(). Although ROWS() itself appear to be non-volatle, referring to cells housing volatile formulas would cause the recalculation of our target formula (See Volatile Excel Functions -Decision Models for the latter point.).

 

[cyrilbrd]

Noted Aladin.
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?
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 number would still return correctly... no?
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.

 

[Aladin Akyurek]

Noted Aladin.
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?
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 number would still work... no?

Yes, it will still work.

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.

I have ever introduced and used

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

which is volatile because ROW() is volatile. ROWS($C$2:C2) is definitely better.

 

[cyrilbrd]

Yes, it will still work.


I have ever introduced and used

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

which is volatile because ROW() is volatile. ROWS($C$2:C2) is definitely better.

I saw something similar to that in here: link.
It was in a Named range the formula was
=ROW(Sheet1!$D$2:$D$8)-ROW(Sheet1!$D$2)+1
So this was volatile, right?

 

[khardu]

Thank you all. It was really helpful. You are stars.

 

[cyrilbrd]

Thanks goes to Aladin.
As I said "the better solution..."

 

[Aladin Akyurek]

I saw something similar to that in here: link.
It was in a Named range the formula was
=ROW(Sheet1!$D$2:$D$8)-ROW(Sheet1!$D$2)+1
So this was volatile, right?

Yes, it's. The difference is that this gives us an integer vector (multiple values) starting at 1, which evaluates to:

{1;2;3;4;5;6;7}

while ROW()-ROW($C$2)+1 (entered in C2) will yield a single item vector:

{1}

ROWS($C$2:C2) instead of ROW()-ROW($C$2)+1 would just yield:

1

which is a scalar value.

An integer vector like ROW(Sheet1!$D$2:$D$8)-ROW(Sheet1!$D$2)+1 is an important construct and is often used as bins in a FREQUENCY formula or in a formula that creates a condition-based sublists.

 

[cyrilbrd]

Thanks Aladin, really very informative.