Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
work only with value without updating
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 he 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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
work only with value without updating
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 C1:D11, click Data Table Enter in "Col input cell" box: E1 (say*) *It can any empty cell outside of the range C2:D11 Click OK, that's it The 10 results will be generated in D2:D11 (if you include D1, you actually have 11 results) Extend the set-up to suit the number of results that you're after -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mh_amri" wrote: 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 he 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
work only with value without updating
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" 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 he 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Since updating Office 2003 to 2007 Excel formulas do not work? | Excel Discussion (Misc queries) | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
When updating a worksheet, how do I create a link updating the sa. | Excel Worksheet Functions | |||
Updating Automatic links doesn't work with arrays | Excel Discussion (Misc queries) |