In Excel, how do I add up all the random values so that they must equal to 1?

Question

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!

Answer 1

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).