libreofficelibreoffice-calcopenoffice-calclibreoffice-basicopenoffice-basic

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


Solution

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