Excel functions to solve analytical question

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a description of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

So ( (Service End Time ) - (Service Start Time))/10 will tell you Minimum break time.


Ceiling( (Minimum break time),1) will round the break time to the nearest whole number.

In post #2 I explained how to attach a sample workbook: please do so. Nobody should be expected to recreate your data in order to be able to help you.

How much of this is an Excel question, and how much of this is the broader "how does anyone anywhere simulate this sort of thing with any programming language?" Because the first step I would take in tackling a problem like this would be to write out, work out the math equations and algorithms I am going to use. Get that well understood before tackling the how to program into Excel part of the problem.

Since I am not at all familiar with the type of problem you are describing, the first thing I did was put "simulation of an mm1 queue" into my favorite internet search. The internet seems to be full of discussions, tutorials, and descriptions of these algorithms.
Wikipedia has this page: https://en.wikipedia.org/wiki/M/M/1_queue
This one looked interesting: http://people.revoledu.com/kardi/tut...ng-System.html

I added Excel to the search string, and found several new hits specific to Excel:
An old spreadsheet, though you probably don't want to just copy this sheet and submit as your result: http://mcu.edu.tw/~hyu/Queue-534.xls
A 4 minute youtube video: https://www.youtube.com/watch?v=wmud0NCf3iw
A longer (45 minute) youtube video (there appear to be several other youtube videos): https://www.youtube.com/watch?v=rcKyxLoVaiM

A final link is to this spreadsheet I built to simulate the Monty Hall problem (not exactly simulation of a queue): https://www.excelforum.com/tips-and-...l-problem.html The thing I want to draw your attention to in this spreadsheet is that it illustrates the generic structure I would try to use as I programmed a simulation like this into Excel. You will note how I arranged all of the calculations needed for a single "trial" in a single row. By doing this, I can simulate as many trials as I want (up to the number of available rows) by simply making copies of the one row.

Another suggestion I would make is that this is a fairly substantial programming problem, and I don't know how many of us here want to tackle the broad, generic "how do I do this simulation in Excel" question. IMO, you will get better help if you pose a specific, narrow question about the specific part of the simulation you are having trouble with. From your spreadsheet, it is clear you already know how to do several of the calculations (generating random numbers, taking averages, and so on). What is the step in the algorithm that you are stuck on? What are the math formulas involved in that step? What data from your sample spreadsheet are the inputs to this step of the algorithm? Where do you want the output for that step to be stored?