Results 1 to 6 of 6

Thread: Recalcultion & random numbers

  1. #1
    Seeker dahowarduk's Avatar
    Join Date
    Jul 2012
    Location
    UK
    Posts
    19
    Articles
    0
    Excel Version
    2104

    Recalcultion & random numbers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have created an Excel Workbook, with the objective of trying to simulate the queues on a Golf Course, depending on the gap between consecutive tee off times.

    Worksheet 1 contains basic data, such as the par for each hole, the minimum, average and the maximum time likely to be spent playing each hole, etc etc

    Worksheet 2 contains 20 sets of ‘random duration times’ for playing each hole, generated from the parameters within Worksheet 1. For the statisticians, I use the probability density function of a Triangular Distribution, and the appropriate Excel formula / function to calculate these random values. Each of these 20 sets contains 100 ‘random’ rounds of golf timings.

    Worksheet 3 performs all of the Queue Calculation for a particular value of the parameter G, which is the gap between consecutive tee off times.

    So I set G=10, and work out the Queue calculation.

    My problem is, that if I now set G=8 (say), all of the ‘random’ durations also get recalculated (all 18x100x20 of them). But I need to compare G=10 with G=8 using exactly the same set of ‘random’ data, not a (somewhat) different set. (otherwise I might have introduced an unnecessary extra variable)

    Is there a way that I can set Worksheet 2, to only recalculate manually, whilst the remainder of the Workbook, recalculates automatically? If so how?
    Or can I set particular ranges in Worksheet 2 to only recalculate manually? If so, how?
    Or is the another better solution?

    Thanks for looking!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,949
    Articles
    0
    Excel Version
    365
    Get Power Query to generate the randomness in a table or tables. New random values only get produced each time the table is expressly refreshed.
    Attach your workbook and I'll try to do that.

  3. #3
    Seeker dahowarduk's Avatar
    Join Date
    Jul 2012
    Location
    UK
    Posts
    19
    Articles
    0
    Excel Version
    2104
    Thanks. But I want to know how to solve my problem rather than someone solve it for me. I'll try to find out about Power Query. Am I correct in assuming that :-
    There is no way that I can set Worksheet 2, to only recalculate manually, whilst the remainder of the Workbook, recalculates automatically? If so how?
    Or can I set particular ranges in Worksheet 2 to only recalculate manually? If so, how?
    regards

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,949
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by dahowarduk View Post
    Thanks. But I want to know how to solve my problem rather than someone solve it for me.
    That's not what I was proposing.
    Since I can't see your worksheet I don't know how you use your random numbers; maybe you have a set of random numbers somewhere that your various formulae refer to, maybe you have one of the random functions as part of your formulas, either way you can get power query to genarate random numbers (1,10, a million, whatever) then either use those random numbers in the same way you're currently using them or replace instances of RND/RandBetween in your formulae with references to power query table cells. Power Query tables values do not change on sheet recalculation.
    I can best help you by demonstrating how to do that on your workbook.
    Quote Originally Posted by dahowarduk View Post
    Am I correct in assuming that :-
    There is no way that I can set Worksheet 2, to only recalculate manually, whilst the remainder of the Workbook, recalculates automatically? If so how?
    Or can I set particular ranges in Worksheet 2 to only recalculate manually? If so, how?
    regards
    I didn't answer that part of your question. The answer, to the best of my knowledge, to both those is no.
    There's something called scenarios in Excel which is meant to facilitate comparing things like your G=8 with G=10 and provide a table of results but I've never used it so I don't know whether it will use the same random values or not (I suspect it will use different random values).

  5. #5
    Seeker dahowarduk's Avatar
    Join Date
    Jul 2012
    Location
    UK
    Posts
    19
    Articles
    0
    Excel Version
    2104
    OK...Thanks for the info and for trying. I think the solution might be to split the workbook into 2 parts. One workbook with the data and the other workbook with the analysis. Cheers

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,949
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by dahowarduk View Post
    OK...Thanks for the info and for trying. I think the solution might be to split the workbook into 2 parts. One workbook with the data and the other workbook with the analysis. Cheers
    Sounds awkward, maybe you're reluctant to explore Power Query, which would be straightforward.

    If you do this in separate workbooks you still won't be able leave it set on Automatic because both workbooks will recalculate (including random numbers) when you change a value that ultimately depends on them. That's the way Excel works. It might work if you have the workbooks in separate instances of the Excel application, but I haven't tested.
    Another way might be to uses INDIRECT in the formulae, which may serve to disguise actual cell/range references from Excel's recalculation engine.
    All very convoluted.

    Regarding: "There is no way that I can set Worksheet 2, to only recalculate manually, whilst the remainder of the Workbook, recalculates automatically?"
    You can do (almost) the opposite; set the whole workbook to Manual, then on any sheet you can Shift+F9 on the keyboard (there's also a button on the ribbon for this) to calculate just that active sheet.
    Last edited by p45cal; 2021-05-20 at 01:12 PM.

Posting Permissions

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