RandBetween with Predetermined Total

[Coneskidcone]

Hi,

I'm working on document that requires some fancy foot work. "E" column is a list of variables with a range between Min and Max (L7 and M7). I'd like to add a button that will refresh the functions in E column until they equal a total specified in J2. I'm open to more efficient methods if this would be fairly time consuming refreshing 600 randomized variables to equal a 5 to 6 digit number. If it's possible, "H" column will need a similar function with reference to Min and Max (J7 and K7) to equal a total specified in K2.

Currently, E column's function looks like this:

Code: Select all

=IF(A2="";"";IF(OR(B2="Personal";B2="");0;RANDBETWEEN($L$7;$M$7)))

E2 will appear blank if A2 is blank. If B2 is labeled "Personal" or left blank, the default is 0. Any other labels (Or lack thereof) will randomize between ranges L7 and M7. "E" column is then added up in L10. I'd like to make L10 match the total specified in J2. What do you guys think would be the best way to complete this?

Thanks, John

[RusselB]

Welcome to the Forums.
If I understand you correctly, you would need to use a loop, which, to my understanding of Calc means using a macro.
Please note that by using generated random numbers there is a possibility that you will lock up your system with it using the resources to try to find a random set of numbers that will meet your requirements.
I can not recommend that you proceed in this manner, as it can stretch the abilities of Calc.

I would suggest looking at a programming language to try to handle this.

[F3K Total]

if your cells are on the first sheet, this code works, if you have some time...see attachment.

Code: Select all

REM  *****  BASIC  *****
global bstop as boolean
Sub Main
 bstop = false
 ThisComponent.lockcontrollers
 oSheet = Thiscomponent.Sheets(0)
 do
    oTargetcell = oSheet.getcellbyposition(9,1)'
    oValueCell = oSheet.getcellbyposition(5,1)'
    ThisComponent.Calculateall
 loop until oTargetcell.Value = oValueCell.value or bstop = true
 ThisComponent.unlockcontrollers
End Sub

Sub stop_Rand
bstop = true
end sub

[Lupp]

E2:E602 has 601 cells.

But my actual questions to the OQer are:
(1) about

I'm open to more efficient methods if this would be fairly time consuming refreshing 600 randomized variables to equal a 5 to 6 digit number.

Is the number 600 you mentioned parenthetically another fact, or would you just estimate that you may need about 600 random numbers in the given range to get the predefined sum?
(2) Is there a use-case?
(3) Are you experimenting concerning statistical models?
(4) Did you ever think about the integer partitions (https://en.wikipedia.org/wiki/Partition_(number_theory)) your preset totals may allow for? Would you actually consider to use a random walk to get such a partition by drawing a sample of 600 as often as needed?

[Up to here the post was basically sensible. The rest was embarrassing nonsense. A serious pratfall. Sorry!]

Assumed a "It's a fixed value." to question (1):
Let L7 =999, M7 =1000 and
a) J2 =599700
b) J2 =599399
What time need would you estimate for the two cases if it's done the "F3K Total" way?
Better: Tell about the use-case.
(For example a you would need 70359079638545882374689246780656119576032161719910400000000000000 {about 7E64} to get an expectation of 1 for the number of samples producing a sum equal to J2. The number of seconds the universe lived since the big bang is about 473000000000000000 {about 4.73E17}. You may need afew universes at your service to solve the example. Tell me if I'm wrong, please.
For example b? Your turn.)

There must have been a blackout on my behalf. The actual probability to get a solution for the example by a single draw was >3.25%.
To demonstrate what I meant you can think of L7=991, M7=1000 and J2=600000 where the probability per draw is 1E-600 if not my next blackout ...

[keme]

You could have a formula with a running sum to guide the draw. This would reduce the required processing from an impractical number of expected passes of recalculation (cf. Lupp's estimate) down to a single pass. This satisfies your request for a more efficient method, but a guided draw is no longer an even probability distribution. If you expect to perform statistics on a "real world simulation", you probably can't use this.

Another approach would be to generate a set of random numbers first, and then scale them up to give the desired sum. This would give the even probability distribution from a single pass, but the numbers would not be integers.

This is just to illustrate why a description of the use case is important for delivering a sensible suggestion.

So: What is it you are doing here?

Also, if you can give us a data set to work with, we are relieved from the labor of building one from scratch to test our formulas, guessing the things you haven't said, and as an added benefit we may be able to identify potential sources of error.

So: Make a copy of your file. Delete/mangle confidential info, and attach it to a posting here.