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


"vandenberg p" wrote:

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


Diane wrote:
: 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.