Question description:
This user has given permission to use the problem statement for this
blog.
I'd like to create a set of random numbers that are selected based on an assigned probability. For example, if a biologist wants to visit two ends of a lake, one 80 percent of the time and the other 20 percent of the time but on random days,how do I create this schedule?
Solved by O. D. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
19/04/2018 - 01:43
Hello! How are you?
Excelchat Expert
19/04/2018 - 01:46
Hi are you still there?
User
19/04/2018 - 01:47
Yes I'm here
Excelchat Expert
19/04/2018 - 01:48
I am thinking about using random function of excel for this, but I need more details regarding the problem to help you build a schedule
Excelchat Expert
19/04/2018 - 01:48
For example, do we have a specified start and end date range where we would select the random dates from?
User
19/04/2018 - 01:50
That hasn't been designed into the survey yet, but we could say for example, 4/11/2020 to 6/30/2020
Excelchat Expert
19/04/2018 - 01:50
My opinion on this specific point of the problem - the creation of the schedule, the probability rates won't still play an important part as it may come in later for probability distribution analysis
Excelchat Expert
19/04/2018 - 01:51
so to be clear about the scope of this session - we will be creating a randomized schedule of dates where the biologist will visit both ends of the river with the range selection of 4/11 to 6/30 year 2020
Excelchat Expert
19/04/2018 - 01:52
i hope to get your agreement on this before we proceed
Excelchat Expert
19/04/2018 - 01:52
thanks
User
19/04/2018 - 01:53
No he can only visit one end of the lake on any one day. I want him to visit the end of the lake that has the most fishing pressure more often than the other end but it still has to be random
Excelchat Expert
19/04/2018 - 01:56
Oh i see, i think the table would look differently if that is the case -- and i think the random values should be which end of the river he will visit for a given period in each day such that the distribution is 80% of the time within that period, he visited End A and 20% End B
User
19/04/2018 - 01:57
That's correct. In a 20 day period for example, I want him to visit lake end A 80% of the time and lake end B 20 percent ot the time, but the days must be random
Excelchat Expert
19/04/2018 - 01:57
Cool, let me try that
Excelchat Expert
19/04/2018 - 02:01
Hi! Thanks for patiently waiting
Excelchat Expert
19/04/2018 - 02:02
I am sending the same file as you can see in the document preview. Kindly open this and press F9 to refresh calculation to randomly assign dates on our 20-day range
[Uploaded an Excel file]
User
19/04/2018 - 02:03
okay will the formulas you used show up?
Excelchat Expert
19/04/2018 - 02:03
Yes, i you wish i could annotate the excel file for you to be able to follow it with more ease
User
19/04/2018 - 02:04
So it appears to me that visits to B are on days 1, 10, 2 and 13?
Excelchat Expert
19/04/2018 - 02:05
Correct, if you press F9, it will randomly reshuffle the dates while maintaining the probability distribution to 80% End A and 20 % End B
Excelchat Expert
19/04/2018 - 02:05
Does that makes sense to you?
User
19/04/2018 - 02:06
Yes. If I wanted to add a longer period of time, say 50 days would I just grab the corner "+" and drag down?
Excelchat Expert
19/04/2018 - 02:08
Plus another step, kindly extend the reference in the formula in column H which reads rank(random number, from the list of random numbers, 1=ascending)
User
19/04/2018 - 02:09
I see. Can this spreadsheet be sent to my email address so I can play around with it?
Excelchat Expert
19/04/2018 - 02:10
Please download this file instead and save in your drive.
[Uploaded an Excel file]
Excelchat Expert
19/04/2018 - 02:11
I'm sorry but this conversation is monitored and we are bound by our policy against soliciting personal information from the client to maintain the integrity of the service platform. I hope you understand
Excelchat Expert
19/04/2018 - 02:12
At this point, if you don't have further questions regarding the proposed solution you may end the session by hovering over the TIME REMAINING section of this chat window and select END SESSION
Excelchat Expert
19/04/2018 - 02:12
Your kind feedback and rating is highly appreciated. Thank you very much!
User
19/04/2018 - 02:12
Sure I understand. Thanks this was very helpful
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
