Written by admin on June 13, 2009 – 9:57 pm
I’m not sure how I’m suppose to do this. Am I suppose to record a macros or is it possible without it? I tried doing it the macros way but it took a very long time to do just one row and excel stopped responding when I tried to this many numbers.
I am have a bunch of numbers generated randomly and I want them all to sum up to 1. Below you can see what I mean.
WM HRB NFLX YHOO BAC OWW DIS FLIR A WFC SBUX
0.79 0.72 0.70 0.31 0.86 0.80 0.41 0.19 0.26 0.65 0.65
0.85 0.74 0.16 0.75 0.93 0.57 0.82 0.29 0.89 0.74 0.42
0.66 0.49 0.30 0.18 0.61 0.14 0.05 0.93 0.86 0.13 0.36
0.96 0.63 0.00 0.18 0.40 0.62 0.79 0.85 0.10 0.25 0.71
0.97 0.38 0.55 0.70 0.09 0.33 0.93 0.72 0.28 0.14 0.10
0.31 0.77 0.93 0.12 0.41 0.50 0.09 0.80 0.09 0.76 0.50
0.78 0.04 0.10 0.88 0.01 0.56 0.58 0.63 0.32 0.59 0.05
0.02 0.20 0.75 0.05 0.34 0.02 0.51 0.50 0.09 0.84 0.87
0.66 0.46 0.47 0.64 0.41 0.88 0.23 0.11 0.03 0.39 0.60
0.33 0.32 0.54 0.26 0.95 0.94 0.67 0.70 0.14 0.80 0.38
0.80 0.09 0.07 0.38 0.89 0.57 0.93 0.07 0.82 0.02 0.00
So I want all the values of WM, HRB, NFLX….etc add up to 1 in the Sum column (not shown here). Right now I have 11 rows and 11 columns, I hope this expand this to possibly 20 rows and 50 columns. I’m adding up the rows not the columns. Any help would be appreciated! Thanks!
Hit Alt+F11 to open VBA editor. Select Insert > Module and paste the code below in the window that opens. I think this is what you’re looking for. If you expand the number of rows or columns, change the value of the constants at the beginning of the code.
Option Explicit
Public Const numCols = 11
Public Const numRows = 11
Sub Generate_Data()
Dim total As Double
Dim adjustment As Double
Dim r As Integer
Dim c As Integer
Dim numArray(numRows, numCols) As Double
For r = 1 To numRows
‘Generate Random Numbers
total = 0
For c = 1 To numCols
Randomize
numArray(r, c) = Rnd()
total = total + numArray(r, c)
Next c
‘Adjust Numbers so sum of row will be 1
adjustment = 1 / total
For c = 1 To numCols
numArray(r, c) = numArray(r, c) * adjustment
Next c
Next r
‘Output numbers to spreadsheet, assuming row 1 is header row and data begins in the second row
For r = 1 To numRows
For c = 1 To numCols
Cells(r + 1, c).Value = numArray(r, c)
Next c
Next r
End Sub
Edit: If you don’t want to use VBA, you could use two separate tables, one with the random numbers, and one where you adjust the numbers based on the totals from the original table. The numbers in the second table would = the original numbers * (1 / the original total of that row).