1

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 spreadsheetData Array

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

Image repeated for reference

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!!

Improve this question
3
  • 1
    Have you considered using VBA? Commented Oct 12, 2016 at 16:35
  • I have considered it, but avoided for two reasons; 1)I'm pretty much a complete novice on VBA, so wouldn't be able to do it myself without some serious time invested in learning it, and also wouldn't be able to quickly alter solutions if they were written for me. 2)I feel like I'm so close with this, and have enough experience with formulae to know it's possible, that switching to VBA seems like a bit of a cop out + adds annoying implementation problems (macros always feel less automatic to me) Commented Oct 12, 2016 at 16:37
  • 1
    Fair enough, especially with the learning curve part. But at some stage it might be a good time investment. There is a limit to what can be achieved with built-in functions. If the logic is too involved it won't be possible, and even if it is possible, the deeply-nested formulas required rapidly become unreadable. On the other hand, a formula guru like @ScottCraner often surprises me in showing how much can be done without VBA. Commented Oct 12, 2016 at 16:51

2 Answers 2

Reset to default
3

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.

![enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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: DataSet2

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.

Improve this answer

8 Comments

What was the final formula? And if my answer, answered the question as it was asked it should be marked as correct. Changing the parameters after an answer is given is not usually acceptable. A new question should be asked. It is possible to make the range dynamic, but more information would be needed. If you stated that the range may change then I could have given a better answer.
Yes @Scott, I see I should have given you the opportunity. I'll summarize; My formula goes in x columns, each y rows long. Whereas your array formula sees which row of numbers to place the random number in based on which row the formula is in (ie. in your array the 1st row ranks the random number in the Cat row), my formula looks in the row with the same title as the contents of the cell directly to its left. Also in my formula each cell has its own associated random number, as opposed to 1 number for the entire set of comparisons.
Now that may be possible to replicate with an array formula, I don't know. But I feel you would need more detail as my problem is quite a bit more complex than the example suggests. Given that the answer I provided means that in my checking cells I don't need to alter my formula at all, and in the array of numbers my formula actually gets simpler, I thought I'd leave the question at that. I felt further detail to pursue your approach would make your answer too specific to be useful to other users, and having solved the problem quite nicely I didn't think I needed a more precise answer.
On a side note, I did intend to mark your answer as correct, as 1) It neatly solves the problem as I presented it, & 2) I think it will be more useful for people with a similar problem than my situation-specific answer. I only hadn't done so until now, as I have a habit of leaving questions for a day or two to accumulate further thoughts/alternative answers. I hope all that's (relatively) clear!
What is the specific formula it wl tell me more than a description. Please post the exact formula.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.