About Random number generator function

[Ashwini Lambture]

Hi All, I have Columns as

A B C D E
1 Age(18-24) Age(25-34) Age(35-44) Age(45-54) Age(55-Above)
2

I want random number generated in all the columnin 2^nd row i.e. A,B,C,D,E such that whose sum(A+B+C+D+E) will be 100 thanks in advance.

 

[Kaper]

And what is the code for Make1D ? some
<code>application.transpose(application.transpose(avInp... ?</code></pre>

 

[Ashwini Lambture]

Hm, third post in a row. Hope moderators are tolerant on this forum

Returning to simple formulas from post #2 on first page and to exactly follow Ashwini's last request. In O1:

=IF(COUNTIF($J1:J1,MAX($J1:$N1))=1,K1,J1)

copy to P1. In Q1 just:

=MAX($J1:$N1)

in R1 (and copy to S1):

=IF(COUNTIF(M1:$N1,MAX($J1:$N1))=1,L1,M1)

copy all J1:S1 down as many rows as needed

in A2 minor change and also copy right and down:

=[COLOR=#ff0000]O[/COLOR]1*100/SUM($[COLOR=#ff0000]O[/COLOR]1:$[COLOR=#ff0000]S[/COLOR]1)

But ... I really liked the code in previous post and distribution it generates - use FREQUENCY array formula to have a look on them.

Hi Kaper, I m not getting how to do it ,.....I have tried with ur formulas but but I m not getting the result what I wanted so can u plz try it in a excel worbook and attach it with your reply .............Thank you very much for ur contribution .Take my columns of ages ok....

 

[István Hirsch]

Here is a practical approach to get the data you need – I do not know much about random properties, so I can not test how much this approach is „random”.

In a new workbook put in A1:

=Randbetween(0,100) then copy right to E1.

Click on A1 and enter into the Name box: „A1:A500000”, press Enter then press Ctrl-D to fill the column with the formula down to row 500000. Do the same with the other 4 columns (my computer sent error message when I tried to fill the other 4 columns by dragging to the right).

Now you have 500000 rows, with five numbers in each row. Copy the whole range, open a new workbook and „Paste Special” the range you copied as „Value”.

Enter in F1:

=IF(AND(MAX(A1:E1)=C1,SUM(A1:E1)=100),C1,"")

and copy down. Now you get around 50 rows where the sum is 100 and column C has the highest value in the row. Copy this table next to it and „Paste special” as „Value”. By putting in order or by filtering, collect the rows which meet both conditions .

The data shows that around 1000000 „random rows” would produce 100 rows that meet both conditions, so you may have to repeat the procedure described above. With 500000 I got 46 such rows (the first 5 columns are the produced random numbers, the 6th colum shows the C value):

Excel Workbook
	A	B	C	D	E	F
1	26	8	27	15	24	27
2	13	17	28	18	24	28
3	28	23	28	13	8	28
4	3	23	29	29	16	29
5	11	26	31	20	12	31
6	20	21	31	12	16	31
7	10	18	31	21	20	31
8	18	18	31	16	17	31
9	27	7	32	12	22	32
10	7	13	32	27	21	32
11	31	24	36	3	6	36
12	16	22	36	19	7	36
13	34	4	36	0	26	36
14	14	15	37	0	34	37
15	7	27	37	22	7	37
16	23	25	38	3	11	38
17	18	10	38	14	20	38
18	14	31	38	2	15	38
19	26	4	39	1	30	39
20	18	6	41	18	17	41
21	12	9	41	2	36	41
22	1	25	41	20	13	41
23	8	24	42	9	17	42
24	17	22	43	2	16	43
25	28	15	43	13	1	43
26	0	22	43	3	32	43
27	10	15	44	17	14	44
28	29	22	44	3	2	44
29	1	28	45	20	6	45
30	41	9	45	3	2	45
31	6	25	46	11	12	46
32	8	5	47	33	7	47
33	10	12	47	24	7	47
34	4	3	47	10	36	47
35	8	37	48	1	6	48
36	13	28	49	1	9	49
37	4	10	52	0	34	52
38	20	10	53	3	14	53
39	10	15	54	5	16	54
40	19	20	57	1	3	57
41	4	29	59	2	6	59
42	0	32	61	6	1	61
43	1	11	61	23	4	61
44	5	24	64	5	2	64
45	5	22	65	3	5	65
46	10	2	84	3	1	84
Sheet

 

[Kaper]

Hi Istvan,

Nice "brute force" approach. Surely "success ratio" ca. 1 /10000 is not high , but who cares, if we just rely on pure computing power. The distribution of results seem to be quite similar to these obtained by shg, especially for columns 1,2,4,5. Column 3 follows different pattern, with modal value near 40-45 long right tail and shorter left tail (obviously no chance for column 3 to fall below 20, because it wouldnt be highest out of 5 received from 100 division)

In the meantime I published the workbook for Ashwini as an attachment to Excel Club random numbers generated to sum up 2 (at very bottom of page). There is some overhead in the spreadsheet, so you can easily delete all after column W and also delete columns G:K.

 

[shg]

And what is the code for Make1D ?

Sorry:

Function Make1D(v As Variant, Optional iBase As Long = 0) As Double()
    ' Returns a 1D iBase-based array of the values in v, which can be a
    ' column or row vector range, a 1D or 2D array, or a scalar

    Dim adOut()     As Double
    Dim rArea       As Range
    Dim cell        As Range
    Dim nOut        As Long
    Dim i           As Long

    If IsArray(v) Then
        If TypeOf v Is Range Then
            ReDim adOut(iBase To v.Cells.Count - 1 + iBase)

            For Each rArea In v.Areas
                For Each cell In rArea.Cells
                    If VarType(cell.Value2) = vbDouble Then
                        adOut(nOut + iBase) = CDbl(cell.Value2)
                        nOut = nOut + 1
                    Else
                        Err.Raise CVErr(xlErrValue)
                    End If
                Next cell
            Next rArea
            Make1D = adOut

        Else    ' get the first column
            ReDim adOut(iBase To UBound(v) - LBound(v) + iBase)
            For i = LBound(v, 1) To UBound(v, 1)
                Select Case VarType(v(i))
                    Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDate, vbByte
                        adOut(nOut + iBase) = CDbl(v(i))
                    Case Else
                        Err.Raise CVErr(xlErrValue)
                End Select
                nOut = nOut + 1
            Next i
            Make1D = adOut
        End If

    Else
        ReDim adOut(iBase To iBase)
        Select Case VarType(v)
            Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDate, vbByte
                adOut(iBase) = CDbl(v)
            Case Else
                Err.Raise CVErr(xlErrValue)
        End Select
    End If
End Function