Forum Discussion

dwarseck's avatar
dwarseck
Copper Contributor
Nov 28, 2025

How should I write this Process?

I am not sure how to do this in function for without going through a long chain of manual brute force steps and hope I'm in the right discussion group for this.  I have hunted the help hits on my search engine, but I'm not even sure if I am asking the right question so I am getting frustrated.

  

The picture below shows where I am now.  I want to set up a Function the will roll a d100 x number of times (with individual rolls reported, not added together) with x equaling the row's Chances cell (an assigned value).  The 1 and 47 are the established Red/Blue cut offs for a different cell to process (outside this question, but will take suggestions if you have them).

 

Ideally they would display as "81, 28, 42" etc. but I am interested in the function lay out.  When it comes to Functions, my knowledge is limited the very basic ones and I am using baby talk when I do use them, so please be kind.

 

Is this even possible or I am I just being crazy/unrealistic?

 

6 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor

    I notice that the original poster, dwarseck​ , has yet to respond. I can appreciate some of the attempts at answering the original question; one of my goals in giving the response I did was to provide help in articulating the situation. That's a skill in itself, vital in Excel as in many other situations; in Excel, I have found that once I can do that, the solution becomes a lot easier. 

  • Vexen_C's avatar
    Vexen_C
    Brass Contributor

    Good question!

    You can join together X number of values using TEXTJOIN and you can use RANDARRAY to make a list of random numbers. So, your cell C6 has a "chances" qty, using that, you can make x number of random numbers into a list:

    =TEXTJOIN(",",FALSE,RANDARRAY(,C6,1,100,1))

    RANDARRAY will regenerate whenever anything is done to the sheet, so, it won't just be static always-the-same numbers. The options are "number of rows" (which I've left blank), number of columns (which is the 'chances'), MIN number, MAX number, and then 'integer' which I've set, so that it only returns whole numbers.

    Example:

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    I agree with mathetes.  At the very least we may help you do whatever you are ultimately trying to do better.  but i believe you want a function to give you an array of rand numbers for each row/person where the 'chances' are the number of elements in the array for that row.  Basically you want RANDARRAY()

    =RANDARRAY(1,C5,1,100,1)

    I'm not sure what you are hoping for with the red and blue.

    On another note, the problem with random numbers in excel is that they will change every time you do anything on the sheet.  so if you want to document the result you will need to take a screen shot or copy and then paste values only to lock the values

  • mathetes's avatar
    mathetes
    Gold Contributor

    I'm quite confident that whatever it is that you're trying to do IS possible.

    The problem so far is that you haven't really done a very good job of describing in plain old English just what that task is. You're trying to use Excel-ese to describe, but as you noted, you're a beginner with Excel. It would actually be better if you left the translation to us, and simply used your best English.

    A few questions might help, beginning with:

    1. WHAT is the start point? It appears as if you're starting with a list of nine people, although obviously that could just be a false inference. You've not revealed any names, just used generic "Person #" so it's also possible that the real task doesn't involve people at all......So please, what's the starting point, or raw Input? What are we beginning with?
    2. WHAT is your desired end result? I can assume, staying with the inference that it's a group of individuals, that the goal is the selection of one (or more?) of the starters, but you need to articulate clearly what the desired end result is. As it stands, you've assumed that, and just attempted to describe a "process," a description that gets muddled in part because you're trying to say it in Excel-ese.
    3. HOW, in English, as you would describe it to an intelligent middle-school student, are you thinking you'd get from the starting point to the finish point? Along the way, perhaps you could explain to that intelligent young person how Red and Blue comes into the scene, why they're associated with something called a "cutoff"...and so forth.

    Again, don't try to put your descriptions in Excel- or Excel-related-terms; that's the task of people here in the forum. Your job is to describe what you'd be doing if you didn't have access to Excel. Maybe that's a good way for you to think about it: what would you do if you were forced to do this all on paper?

    If you'd rather work on extending your Excel capabilities--though it may take longer, it's always a great way to really learn--you would do well to look over this page of Excel functions, grouped in to an intelligent set of categories, and accompanied by clear definitions and examples. But even to use it, I'd seriously recommend, for your own sake, working to describe the process in English.

    • Vexen_C's avatar
      Vexen_C
      Brass Contributor

      I can answer the "WHAT" by reading the post: He wants a function that will produce x number of random numbers between 1 and 100, with each result listed something like "81, 28, 42"; it's not entirely important, he's more interested in seeing how the formula might be laid out.

      As for the 'starting point' etc, I think his starting point is that he's got an Excel sheet on which he's listed 9 players, and he wants to generate some random numbers for each of them. It doesn't matter what the people are called. It's fine that they are just called Person1, etc. That's what we're beginning with.

      It's clearly the very early, test-phase of something, to see if Excel can do it, and how he might get started. So I doubt you'll get the kind of answers you're looking for.

      As for the "cutoffs" - that means a value above which (or below which) it will get ignored/annulled, etc. As he's said it's outside of the question he's asking, I recommend ignoring that part, at least for now.

Resources