Question about a function in Excel RAND()

Posted on 2011-10-03
Last Modified: 2012-06-27
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
Question by:sharkyboy
    LVL 12

    Accepted Solution

    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,

    LVL 80

    Expert Comment

    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
    LVL 17

    Expert Comment

    Try this without macros - using the RANK function:

    LVL 17

    Expert Comment

    Sorry byundt - crossed over in posting - mine is very similar to yours

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    Excel file corrupted. 13 28
    Excel formula 5 31
    Protecting an object 3 23
    Dynamic Bar chart in Excel 5 9
    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now