CODEWITHSUNDEEP
libreofficelibreoffice-calcopenoffice-calclibreoffice-basicopenoffice-basic
OmNamohBhagwateVasudevaye
OmNamohBhagwateVasudevaye

Reputation: 113

How to run a formula until it meets a certain criteria?

So, I have a formula ( =INDEX(Sheet1.A1:F15,RANDBETWEEN(1,15),RANDBETWEEN(1,6)) ) that returns a random number in the sheet. But, how to run the formula until the returned number is less than or equal to 25 ?

I thought of using for..next.. but couldn't get it how to run ...

Upvotes: 1

Views: 133

Answers (1)

JohnSUN
JohnSUN

Reputation: 2539

Welcome!

As @thebusybee pointed out in his comment, a macro for this task is much easier than using built-in functions. As rightly pointed out @tohuwawohu, pre-filtering the values makes things a lot easier. The macro code could be, for example, like this

Option Explicit 

Function getRandValue(aValues As Variant, nTypeCriteria As Integer, dCriteriaValue As Variant) As Variant
Rem Params: aValues - array of values,
Rem    nTypeCriteria - -2 less then, -1 not more, 0 equal, 1 not less, 2 more than
Rem    dCriteriaValue - value to compare
Dim aTemp As Variant 
Dim i As Long, j As Long, k As Long
Dim bGoodValue As Boolean 
    k = UBound(aValues,1)*UBound(aValues,2)
    ReDim aTemp(1 To k)
    k = 0
    For i = 1 To UBound(aValues,1)
        For j = 1 To UBound(aValues,2)
            bGoodValue = False
            Select Case nTypeCriteria
                Case -2
                    bGoodValue = (aValues(i,j) < dCriteriaValue)
                Case -1
                    bGoodValue = (aValues(i,j) <= dCriteriaValue)
                Case 0
                    bGoodValue = (aValues(i,j) = dCriteriaValue)
                Case 1
                    bGoodValue = (aValues(i,j) >= dCriteriaValue)
                Case 2
                    bGoodValue = (aValues(i,j) > dCriteriaValue)
            End Select 
            If bGoodValue Then
                k = k+1
                aTemp(k)     = aValues(i,j)
            EndIf
        Next j
    Next i
    If k<1 Then 
        getRandValue = "No matching values"
    ElseIf  k=1 Then 
        getRandValue = aTemp(k)
    Else
        getRandValue = aTemp(Rnd()*(k-1)+1)
    EndIf 
End Function

Just put a call to this function in a cell in the form =GETRANDVALUE(A1:F15;-1;25)

Upvotes: 2

Related Questions