Recalcultion & random numbers

dahowarduk

New member
Joined
Jul 15, 2012
Messages
25
Reaction score
0
Points
1
Location
UK
Excel Version(s)
2104
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!
 
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.
 
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
 
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.
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).
 
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
 
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:
Back
Top