Avatar of sharkyboy
sharkyboy
 asked on

Question about a function in Excel RAND()

Okay, I have 25 rows I need 15 rows with the value "1" and 10 other row with the value "-1". But the order have to be random so I can change the order everytime I want. The function RAND() lets me put the value 1 or -1 but my problem is that I need to have a specific number of each value at a random order.

Thank you
Microsoft Excel

Avatar of undefined
Last Comment
andrewssd3

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
sdwalker

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
byundt

Without using macros:

1) Put the formula =RAND() in 25 cells. I used A1:A25
2) Copy down the following formula for your random mix of 1 and -1:
=IF(A1>=LARGE(A$1:A$25,15),1,-1)              returns 15 with 1, 10 with -1
andrewssd3

Try this without macros - using the RANK function:

 rand.xlsx
andrewssd3

Sorry byundt - crossed over in posting - mine is very similar to yours
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck