Generating Random Numbers with comparing/aligning

[FiberFlux]

Hi to all.
I am new in this forum, and I might be explaining myself in an obscure way. But please don't hesitate to ask if I write something that is completely rubbish.
I have read the forum post http://www.excelforum.com/excel-form...obability.html and used the provided downloads to work on.
I have managed to get the fundamentals working - but I can't figure out how to make the random numbers appear as I wish.
PrintScreen.jpg
As you can see from the screendump above - then I have two columns with random numbers.
I am trying to make the random numbers in column A and G to be in the same range.
This should be done accordingly to the definitions i row 3-8.
So the output should be more like:

3	8
12	44
22	77
42	108
66	222

I hope that it makes sense....and if not - do please ask what I mean

[tim201110]

did you mean that?

[Phuocam]

=MATCH(RAND()*100,{0,6,16,36,66,86},1)-1

[FiberFlux]

Hi tim201110 and Phuocam

Thanks for your inputs. Both solutions where not the answer that I was looking for. As seen in the sheet above I need the columns A and G to be filled with random numbers, but numbers that will stay inside the range defined in row 3-8. In the sheet you can see row 11 where data in column A is 1 and in column G is 85. That should have been column A=1 (a number between 1-5) and column B=a number between 5-12. So forth all the way through 10000 rows - but with random numbers defined from row 3-8. Do I make myself understandable?

[JeteMc]

If I understand the query correctly the following formula will provide random numbers for column A:

Please Login or Register  to view this content.

Attached is a file that demonstrates how the formula is applied.
Let me know if you have any questions.

[FiberFlux]

Hi JeteMc,
You are half the way :-)
The formula that you provide gives the numbers needed - BUT - as seen in Column C there is a difference in the cumulative %. Out of the 10000 rows that needs to be filled with random numbers only 8% should be in the range of row 3. 25% should be in the range of row 4. 39% should be the range of row 5. 17% should be in the range of row 6. 8% should be in the range of row 7 and finally 3% should be in the range of row 8. Totally 100% and 10000 rows.
Then the next tricky part comes. Column G should also be filled with random numbers like Column A. But this time accordingly to the cumulative % in Column I. Also the two Columns A and G should be synchronized with each other - meaning that the output of each row and the two columns should be accordingly to the % definition and interval specified in row 3-8.
Hope that this will make the task more understandable.
Looking forward to seeing your reply.

[JeteMc]

This solution uses a helper column (L) which may be hidden for aesthetic purposes. The formula for the helper column, which is modified from sulakvea's (another thread), sets the parameters for the synchronous distribution of the random numbers in columns A and G and reads as follows:

Please Login or Register  to view this content.

The formula for column A is:

Please Login or Register  to view this content.

The formula for column G is:

Please Login or Register  to view this content.

Let me know if you have any questions.

[FiberFlux]

Hi again JeteMc,

This is really BEAUTYFULL....Almost exactly as I wanted it.
I could only dream of one more 'option' - and that is to set a median of the interval in some way.
IE: Row 3 - Column D-E (1-5) is most likely to be 4 or 5 and with column J-K is most likely to be 10. Same goes for row 8 -Column D-E (100-150) is most likely to be 118 and column J-K is most likely 324.
Is it possible to 'build' a slider of some kind behind each of the rows 3-8 / columns D-E and J-K where the median interval could be adjusted. The Median % could then be determined by a standard factor for each of the column pairs.
IE: Row 3 - D-E there is 60% chance that the value should be 4 or 5 - the remaining 40% is equally distributed upon the remaining interval values (1,2,3). J-K there is 60% chance that the value should be 9 or 10.
IE: Row 8 - D-E there is 60% chance that the value should be between 104 or 125 - the remaining 40% is equally distributed upon the remaining interval values. And etc. etc.
I hope that you have the answer to my question - And I really appreciate the help you are given me.

[JeteMc]

You might add rows and accomplish something close to what you are asking. change row 3 so D3 is 4 and E3 is 5. Insert a new row 4 where D4 is 1 and E4 is 3 (and new row 5 where D5 is 1 and E5 is 3 which will be needed due to separating the 5-12 range on the right side). Add another index number (.048) and value (7) in choose. The 'random busting' part, in my mind, would be on the right side where J3 and K3 would be 10. J4 would be 5 and K4 would be 9, you would then need to insert another row so that J5 could be 11 and K5 could be 12. Reset the Choose again adding index number .064 and value 8. This would mean that 10 would be chosen 60%, 5,6,7,8 or 9 would be chosen 20% and 11 or 12 would be chosen 20% of the original 8% that was designated to be 5-12. (I hope that makes some amount of sense.) I did not tackle any of the other ranges in the attached file as I am not sure that this approach is acceptable.

[FiberFlux]

I can see the modifications and they seems to be the right thing. Unfortunately I can't sort on Column L. I wanted to sort the numbers in column A+G accordingly to Column L. That way I can better see the effect of the new formulas. Do you have any idea on how to 'sort' this out.

[JeteMc]

1) Select L13:L1012
2) Copy (Ctrl+C)
3) Paste values
4) Sort AtoZ
Note: that I pasted the formula preceded by an apostrophe to N13 so I could use it later without having to retype.

[FiberFlux]

SUPER finally got it perfect. I have expanded the new formula lines and added the new percentages and it is working excellent. Thank you so much for your help.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.