Formulae help please?

[Andy Moore]

Hi,

I have a =rand()25 formula entered into a cell, which is copied across the row. Is there a way that I can tell the formula not to repeat the rand result in the whole row?

Many thanks

Andy

 

[Jon von der Heyden]

Hi Andy

Firstly what is your actual formula? RAND()25 is not a valid formula. Are you wanting random numbers between 1 and 25?

 

[kpark91]

You mean you have a rand() formula across 25 cells ranging from 1 - 25 and you don't want any of the cells to repeat?

 

[Andy Moore]

Hi again,

Probably not making myself clear! I want a random number from 1 to 25, copied across the row, but not reapeating any number already generated.

Thanks for your patience.

Andy.

 

[BenMiller]

Put this in A2 (will not work in A1 - needs an anchor cell) and drag across. Must be entered as an array formula (confirmed with CTRL+SHIRT+ENTER).

=SMALL(IF(COUNTIF($A1:A1,COLUMN($1:$25))<>1,COLUMN($1:$25)),1+INT(RAND()*(25-COLUMN()+COLUMN($A2))))

 

[Jon von der Heyden]

Well, Ben... That's a cool formula. Where did you pinch it from?

A real head-scratcher, but it appears that the last item will always be the upper value (in this case 25). It also processes a huuuuuuuge array. Perhaps this is one of the few cases I might prefer a UDF? Still, that formula is cool!

Using a UDF:

Place this in a standard module:

Public Function RandBetweenA(ByVal dblLower As Double, _
                             ByVal dblUpper As Double, _
                             Optional lngDecimals As Long = 5, _
                             Optional blnVolatile As Boolean = False) As Variant()
    
    Dim rngArea As Range, lngItem As Long, lngRow As Long, lngCol As Long
    Dim varResult As Variant, varResults() As Variant, varTemp() As Variant
    Dim lngMaxIterations As Long
    
    If blnVolatile Then Application.Volatile
    
    If StrComp(TypeName(Application.Caller), "Range", vbBinaryCompare) <> 0 Then
        Call Err.Raise(Number:=vbObjectError + 1024, Description:="RandbetweenA is only callable from a range!")
        Exit Function
    End If
    
    lngMaxIterations = dblUpper * (10 ^ lngDecimals) - dblLower * (10 ^ lngDecimals) + 1
    
    Set rngArea = Application.Caller
    ReDim varResults(1 To rngArea.Rows.Count, 1 To rngArea.Columns.Count)
    ReDim varTemp(1 To rngArea.Count)
    
    For lngRow = 1 To rngArea.Rows.Count
        For lngCol = 1 To rngArea.Columns.Count
            lngItem = lngItem + 1
            If lngItem <= lngMaxIterations Then
                Do
                    varResult = Evaluate("rand()*(" & dblUpper & "-" & dblLower & ")+" & dblLower)
                    varResult = Round(varResult, lngDecimals)
                Loop Until IsError(Application.Match(varResult, varTemp, 0))
            Else
                varResult = CVErr(xlErrNum)
            End If
            varTemp(lngItem) = varResult
            varResults(lngRow, lngCol) = varResult
        Next lngCol
    Next lngRow
    
    RandBetweenA = varResults
    
End Function

It needs to be used as an array-entered formula. In other words, select the entire range where you wish the results to appear, type the formula, and commit with control+shift+enter. In this example I selected A1:A27, then typed [highlight]=RandBetweenA(1,25,0,FALSE)[/highlight], and then hit control+shift+enter.

Excel 2010
	A
1	3
2	19
3	2
4	12
5	21
6	15
7	11
8	17
9	14
10	16
11	8
12	5
13	18
14	22
15	25
16	9
17	10
18	1
19	20
20	4
21	7
22	24
23	6
24	13
25	23
26	#NUM!
27	#NUM!
Sheet1

This UDF takes the following syntax:
=RandBetweenA(lower, upper, decimals, volatile)

The lower and upper are obvious. The decimals argument (default 5) allows you to specify how many decimal places you want in your random numbers. The volatile (default false) determines whether or not you want the function to recalculate each time recalculation is invoked.

 

[BenMiller]

Well, Ben... That's a cool formula. Where did you pinch it from?

Actually modified a formula I saw in the Mr. Excel book just yesterday - Excel Gurus Gone Wild Page 46.

 

[Jon von der Heyden]

I should get royalties on that book! Read the foreword...

 

[BenMiller]

I don't have it in front of me but I could imagine you're in there... After all, you INVENTED the cool kids on the block

 

[Jon von der Heyden]

After all, you INVENTED the cool kids on the block

Well it's a very small, select and exclusive club!