work only with value without updating

[mh_amri]

A B C
1 0.1 =sum(A1:A4)
2 0.3 =sum(A1:A4)
3 0.7 =sum(A1:A4)
4 0.4 =sum(A1:A4)

Hello , i have many random number that maked with =rand() function and
placed in Column "A"
and need to sum them in one cell {for example here: sumed in B1}

and must do it in many time , in other world i need sum of many random
number in many time and each time must give a seperate number of sum.
but whenever i Press F9 the whole sum number change.{in table above i model
it in 4 time , b1 and b2, b3,b4,i need 4 sum of random number and each of
them must be seperate}
to solve this problem i change the C1 formula to this: =if(C2=0,B1,C1) and
use auto fill step by step

with this formula c1 become equal to b1 and not change with change of random
number
but this have 3 realy big problem and that is you must use auto fill step by
step(it mean you must fill c2, then c3 and so on.and can't fill a range of
cell to have sum of past value that maked with random function, and take
effect by new random number and just imagine you must do it thousand time}
and other problem is , this formula is a loop!

and the other problem is whenever i want average the sum numbers excel show
"0"
to solve it i copy the column C1 and paste special it (only value) and then
average it

so my question is , is it any better way to do it in no time or i must kill
my time with this stupid solution of mine?

 

[Max]

To repeatedly generate it all at one go,
you could easily create a one variable data table

Let's say you want to generate 10 results
Put in D1: =B1 (just a simple link pointing to the formula cell in B1)
List the number series in C2:C11 : 1,2,3 ... 10
Select C111, click Data > Table
Enter in "Col input cell" box: E1 (say*)
*It can any empty cell outside of the range C211
Click OK, that's it

The 10 results will be generated in D211
(if you include D1, you actually have 11 results)

Extend the set-up to suit the number of results that you're after

 

[Jim Cone]

Here is some VBA code that retains the sum of each set
of random numbers generated, in case you prefer that alternative...
'--
Sub AddThemUpManyTimes()
Dim N As Long
Dim rngAll As Range

'will contain the random numbers (Column A)
'adjust size as necessary
Set rngAll = Range("A1:A4")

'dummy cell used by the loop below.
Range("C1").Formula = "=Sum(" & rngAll.Address & ")"

'fills range with random number formula
rngAll.Formula = "=Rand()"

'adds sum of the randoms to column B ( ten separate totals)
For N = 1 To 10
Cells(N, 2).Value = Range("C1").Value
Next

'clean up
Set rngAll = Nothing
End Sub
'--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"mh_amri" <[email protected]>
wrote in message
A B C
1 0.1 =sum(A1:A4)
2 0.3 =sum(A1:A4)
3 0.7 =sum(A1:A4)
4 0.4 =sum(A1:A4)

Hello , i have many random number that maked with =rand() function and
placed in Column "A"
and need to sum them in one cell {for example here: sumed in B1}

and must do it in many time , in other world i need sum of many random
number in many time and each time must give a seperate number of sum.
but whenever i Press F9 the whole sum number change.{in table above i model
it in 4 time , b1 and b2, b3,b4,i need 4 sum of random number and each of
them must be seperate}
to solve this problem i change the C1 formula to this: =if(C2=0,B1,C1) and
use auto fill step by step

with this formula c1 become equal to b1 and not change with change of random
number
but this have 3 realy big problem and that is you must use auto fill step by
step(it mean you must fill c2, then c3 and so on.and can't fill a range of
cell to have sum of past value that maked with random function, and take
effect by new random number and just imagine you must do it thousand time}
and other problem is , this formula is a loop!

and the other problem is whenever i want average the sum numbers excel show
"0"
to solve it i copy the column C1 and paste special it (only value) and then
average it

so my question is , is it any better way to do it in no time or i must kill
my time with this stupid solution of mine?