+ Reply to Thread
Results 1 to 10 of 10

Excel functions to solve analytical question

  1. #1
    Registered User
    Join Date
    03-19-2017
    Location
    NETHERLANDS
    MS-Off Ver
    2010
    Posts
    6

    Question 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. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,107

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,853

    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.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  4. #4
    Registered User
    Join Date
    03-19-2017
    Location
    NETHERLANDS
    MS-Off Ver
    2010
    Posts
    6

    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. #5
    Registered User
    Join Date
    03-19-2017
    Location
    NETHERLANDS
    MS-Off Ver
    2010
    Posts
    6

    Re: Excel functions to solve analytical question

    Quote Originally Posted by mehmetcik View Post
    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. #6
    Registered User
    Join Date
    03-19-2017
    Location
    NETHERLANDS
    MS-Off Ver
    2010
    Posts
    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,107

    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. #8
    Registered User
    Join Date
    03-19-2017
    Location
    NETHERLANDS
    MS-Off Ver
    2010
    Posts
    6

    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-19-2017
    Location
    NETHERLANDS
    MS-Off Ver
    2010
    Posts
    6

    Re: Excel functions to solve analytical question

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

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,671

    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.
    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need Help with Vlookup and Index Functions to Solve for Error
    By trandle in forum Excel General
    Replies: 13
    Last Post: 05-22-2015, 05:25 PM
  2. Replies: 0
    Last Post: 11-10-2008, 12:26 PM
  3. Converting analytical data
    By SageRhys in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-09-2008, 02:54 PM
  4. solve Another Question
    By MJB10038 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-10-2007, 05:19 PM
  5. solve:General Question
    By Jared Jenner in forum Excel General
    Replies: 1
    Last Post: 07-18-2006, 03:15 PM
  6. [SOLVED] Correlation - Which Analytical Function to Use
    By Pasko1 in forum Excel General
    Replies: 2
    Last Post: 10-06-2005, 08:05 PM

Tags for this Thread

Bookmarks

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