Is this possible?

[Guest]

I have a run of numbers each run contains about 10 or so numbers), all with
eight decimal places. The total of this run must equal 1.00000000. We have
loads of these to review. Is there a way to create a formula that would look
at the sum and if it does not equal 1.00000000, then it would go up into the
run of numbers and randomly adjust the numbers so that the displayed run
equals 1.00000000

We know we can sum and then manually adjust - that is just not feasible.

 

[vandenberg p]

Hello:

It is not clear to me exactly what you wan to achieve but here are two
options.

The data is in column A and Columns B and C contain the neede values

0.0742475604114266 =A1/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A1,A1)
0.0487834328689492 =A2/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A2,A2)
0.0594267736749775 =A3/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A3,A3)
0.0819045549470932 =A4/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A4,A4)
0.0854958858002441 =A5/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A5,A5)
0.055458395973614 =A6/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A6,A6)
0.079250188292968 =A7/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A7,A7)
0.0872201801436292 =A8/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A8,A8)
0.0162012655158353 =A9/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A9,A9)
0.0755702998010725 =A10/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A10,A10)
=SUM(A1:A10) =SUM(B1:B10) =SUM(C1:C10)
=RANDBETWEEN(1,10)

The first column contains the numbers. IN this example they add up
to .6635584.... In order to force them to add 1.000 the next
distributes the difference between the sum and 1 about .3364...
across the entire 10 numbers, in proportion--not random.

The next column add the difference to a single determined by
the value of the randbetween() value.

I enter the above in a spreadsheet you will see that both
columns add to 1. Since this is the programming group
you could convert to VBA fairly easily. But you need
to decide which methodolgy you want to use.
Usually these sorts of problems are solve with
proportions.

Pieter Vandenberg


: I have a run of numbers each run contains about 10 or so numbers), all with
: eight decimal places. The total of this run must equal 1.00000000. We have
: loads of these to review. Is there a way to create a formula that would look
: at the sum and if it does not equal 1.00000000, then it would go up into the
: run of numbers and randomly adjust the numbers so that the displayed run
: equals 1.00000000

: We know we can sum and then manually adjust - that is just not feasible.

 

[Guest]

Depends on the setup, goalseek will do what you want, you just have to
randomize which one will be the variable. if they are in columns, and 1-9 are
the numbers you can adjust, and 10 is the one to be adjusted:
Randomize
mycell = Int((9 - 1 + 1) * Rnd + 1)
Range("A10").GoalSeek Goal:=1, ChangingCell:=Range("A" & mycell)

try that out and ask whatever questions you have from there

 

[Guest]

Peter,

What am I doing wrong here? I keep getting the name# error in the third
column?
THanks

0.0742475604114266 =A1/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A1,A1)
0.0487834328689492 =A2/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A2,A2)
0.0594267736749775 =A3/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A3,A3)
0.0819045549470932 =A4/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A4,A4)
0.0854958858002441 =A5/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A5,A5)
0.055458395973614 =A6/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A6,A6)
0.079250188292968 =A7/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A7,A7)
0.0872201801436292 =A8/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A8,A8)
0.0162012655158353 =A9/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A9,A9)
0.0755702998010725 =A10/$A$11 =IF(ROW()=$C$12,(1-$A$11)+A10,A10)
=SUM(A1:A10) =SUM(B1:B10) =SUM(C1:C10)
=randbetween(1,10)

Thanks. God bless