Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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
0
sharkyboy
Asked:
sharkyboy
  • 2
1 Solution
 
sdwalkerCommented:
This will do exactly what you need.  First, it randomly selects the 1's, then it goes back and fills in the zeroes.

Let me know if you have questions

Good luck,

sdwalker
eeTest.xlsm
0
 
byundtCommented:
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
0
 
andrewssd3Commented:
Try this without macros - using the RANK function:

 rand.xlsx
0
 
andrewssd3Commented:
Sorry byundt - crossed over in posting - mine is very similar to yours
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now