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

Last Comment

andrewssd3

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

sdwalker

10/3/2011

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

10/3/2011

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

10/3/2011

Try this without macros - using the RANK function:

rand.xlsx

andrewssd3

10/3/2011

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