Excel: Formula to place a random number in an array

Question

This one has got me seriously stumped, so I thought I'd share it with you guys and see what I get :)

General Problem

I have the following data in a spreadsheet

As you can see I have two identical sets of headings 'Cat, Dog, Man...' (the precise names do not matter)

These two sets are classed under either the 'From' column or the 'To' row. The bulk of the table is an array of numbers between 0 and 1, or empty cells.

Essentially what I want to do is find where a given Rand() number ranks along each row. I then return the item from the 'To' row corresponding to where the random number would be placed.

i.e. Reading along the 1st Cat row, a random number (between 1 and 0 too) is ranked. If it's,say, between 0 and 0.3658... I return Cat, 0.7193... and 1 gives me Van

So ideally I return the value from the To set of headings which is vertically above the upper bound within which the Rand() number lies.

---

Attempted Solution

To achieve this ranking I've been using the Match(value,array,0) function (0 as my numbers are in ascending order).

A simplified version of my formula is therefore:

=Index(To_Headings,MATCH(RAND(),From_Row,0))

where To_Headings is the array E3:I3 and From_Row is an array I generate using a further formula, resulting in D#:I# (# being a row number which is related to the From headings, so must be an integer between 4 and 8)

However if you are particularly observant, you can see this is where my solution falls short

As I say,ideally I want to find the upper bound of where RAND() lies, as this is always in the same column as my desired output To heading. MATCH() with a parameter of 0 returns the lower bound of where RAND() lies. Typically this is the column 1 to the left of the desired column.

e.g. Reading along the Cat row again - for a random number of 0.5, bounds within which it lies are 0.3658... and 0.7193... .The upper bound is directly below Dog, my desired output. The lower is 1 column to the left of the desired 1, so in my formula I simply shift back to the right when reading off using Index

HOWEVER the blank cells render this useless. For a random number between 0.719... and 1, the lower bound is now two columns to the left of the upper bound. In other instances it can be 3 or 4, in fact any number. This is because the blank cells push the upper bound further right.

---

Right, bearing all that in mind, can anyone tell me how to rank the RAND() number so it gives me the upper bound? Of course I've tried Match with -1 as the parameter, however because that requires descending order the problem flips too!

I'm thinking I could try counting if there are any blanks to the right of the lower bound, and offsetting my INDEX by that many instead of just 1, but I can see that will add a lot of lines to my code, and I really need to keep it streamlined, as it will be running in about 10000 cells!!

Answer 1

I've found another very simple answer through experiment with the Match function.

All you have to do is fill those blanks with the number on the left. Since Match doesn't look for the first instance of a number greater than the random number, but instead the last, by filling in the blanks all the adjustment is done for you.

So the example picture now becomes the following:

So for example, reading along the Cat row again, 0.719... is repeated. For a random input of 0.8, instead of stopping at the Dog column as before (since 0.8<1, therefore 0.719... is the biggest number smaller than the random input), the formula now stops 1 column to the left of the Upper bound every time.

Now I admit @ScottCraner gave a perfect answer to my problem as stated, however an Array formula is stuck to only one size (without VBA) whereas cell formulae can automatically fill a new range, so for my application I cannot use that answer. So I thought I'd add on this solution as a cell formula approach.

Answer 2

You can use this array formula.

Since your data is arranged in ascending order, this will find the first cell in which the random number is less than or equal to the number in the dataset:

=INDEX($E$3:$I$3,MATCH(TRUE,$J$2<=E4:I4,0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.