## RandBetween with Predetermined Total

### RandBetween with Predetermined Total

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   Expand viewCollapse view
`=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
Apache OpenOffice 4.1.7 with Win10
Coneskidcone

Posts: 1
Joined: Sat Apr 04, 2020 12:18 am

### Re: RandBetween with Predetermined Total

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: RandBetween with Predetermined Total

if your cells are on the first sheet, this code works, if you have some time...see attachment.
Code: Select all   Expand viewCollapse view
`REM  *****  BASIC  *****global bstop as booleanSub 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.unlockcontrollersEnd SubSub stop_Randbstop = trueend sub`
Attachments
randbetween.ods
MMove 1.0.6
• Extension for easy, exact positioning of shapes, pictures, controls, frames ...
my current system
• Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer

Posts: 972
Joined: Fri Dec 16, 2011 8:20 pm

### Re: RandBetween with Predetermined Total

E2:E602 has 601 cells.

But my actual questions to the OQer are:
Coneskidcone wrote: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?
(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.

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 ...
Last edited by Lupp on Sun Apr 05, 2020 2:37 am, edited 2 times in total.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: RandBetween with Predetermined Total

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.

keme
Volunteer

Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway