RandBetween with Predetermined Total

Discuss the spreadsheet application

RandBetween with Predetermined Total

Postby Coneskidcone » Sat Apr 04, 2020 1:03 am


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

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
Posts: 1
Joined: Sat Apr 04, 2020 12:18 am

Re: RandBetween with Predetermined Total

Postby RusselB » Sat Apr 04, 2020 2:16 am

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 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.
User avatar
Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: RandBetween with Predetermined Total

Postby F3K Total » Sat Apr 04, 2020 10:43 am

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 boolean
Sub Main
bstop = false
oSheet = Thiscomponent.Sheets(0)
    oTargetcell = oSheet.getcellbyposition(9,1)'
    oValueCell = oSheet.getcellbyposition(5,1)'
loop until oTargetcell.Value = oValueCell.value or bstop = true
End Sub

Sub stop_Rand
bstop = true
end sub
(12.45 KiB) Downloaded 13 times
    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
Posts: 972
Joined: Fri Dec 16, 2011 8:20 pm

Re: RandBetween with Predetermined Total

Postby Lupp » Sat Apr 04, 2020 11:05 pm

E2:E602 has 601 cells.

But my actual questions to the OQer are:
(1) about
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?
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 ...
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
User avatar
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: RandBetween with Predetermined Total

Postby keme » Sun Apr 05, 2020 12:58 am

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.
User avatar
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Return to Calc

Who is online

Users browsing this forum: Hanzo and 15 guests