# Excel functions to solve analytical question

1. ## Excel functions to solve analytical question

I am a novice in Excel functions.And have encountered this problem for my management science course.I would be grateful if someone can help me in it. the question is a part of the business simulation model.I need to find the function that can build -
" after every 10 minutes, the server has to rest for 1 minute. He first finishes the service of a customer and then takes the rest".
I have a service start time and server service completion time for each customer.Can any help me with the logic function that can help me build it with the inputs from the cell of service start time and service completion time of servers.If he starts with a customer, take will finish with him even if it crosses 10 mins and takes the rest.Thank you for your help in advance. :)

2. ## Re: 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.

3. ## Re: Excel functions to solve analytical question

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.

4. ## Re: Excel functions to solve analytical question

Please find the breakdown of columns that I have and the formula attached to them.The question is to delay the start time by 1 minute, after every 10 min of service time.Also if there is an exsisting customer in the system, then the server will finish him and then take the break even if the server crosses 10 min while serving him.

1) Customers = (1-1000 nos)

2) Interarrival Arrival Service =-LN(RAND())*arrival rate

3) Arriva time of next customer = Interarrival time + arrival time for earlier customer

4) Service start time = ?

5) Waiting time = Service start time - Arrival time

6) Service time = =-LN(RAND())*service rate

7) Completion time = service start time + service time

5. ## Re: Excel functions to solve analytical question

Originally Posted by mehmetcik
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.

Thank you for your assistance.I am not sure it will solve the prob.I have posted below the formula of each terms.I would be grateful if you could have a look at it and help me.Thank you once again.

6. ## Re: Excel functions to solve analytical question

Thank you for your reply.I just posted the formula for each cells below.I would be grateful if you could help.

7. ## Re: Excel functions to solve analytical question

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.

8. ## Re: Excel functions to solve analytical question

Added the excel file.Service start time has to be programmed so that after every 10 mins of service time, there is a delay of 1 min in the start of service start time.If the customer is already in the system then the service time will restart after it finished it.

9. ## Re: Excel functions to solve analytical question

Sorry I missed the attachment earlier.I just added the attachment.Looking forward for your inputs.

10. ## Re: Excel functions to solve analytical question

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.
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 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?

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1