Search code examples
excelmathexcel-formulaformula

[Excel][Math] Formula to generate numbers between two limits

I am trying to fill a column in Excel with values that are generated randomly but the sum of all the values total to the end.

Example:

Starting value = 320

Final value = 350

Need to generate 2 more values which have random difference between them but total to 350 at the end, as in: 2nd val = 12, 3rd val = 18.

The script/formula should generate different values when run next (for another table etc.). It may generate, for the same starting and final values, 15 and 15 or 8 and 22 for the 2nd and 3rd values respectively etc.

Basically what the formula should do is: Find the difference between the starting and final values then randomly add a number to create the 2nd entry. Now the third entry should follow the same pattern but the value generated should end up totaling to the final.

The example is only for 2 values but I'm working on tables ranging from 15-30+ values.

I don't know if Excel can do it, or if there's a mathematical formula that will work here.

Thanks in advance for all the help!

Solution

  • You need something like this:

    formulas

    That gives:

    values

    The trick is to generate a list of uniform random numbers between 0 and 1 and then scale those numbers up the total that you're looking for.