Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.