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 recursively until the returned number is less than or equal to 25 ?
Why have you tagged writer (text document processing) and base (database interface)? Obviously, you formula is a Calc one and your question is Calc-related.
So, please retag and while you’re at it, edit your question to mention OS name, LO version and save format.
I would like very much to be told what the questioner actually wants to achieve in the context where he (f/m) created the table from which values shall then be randomly chosen with the help of the INDEX() function…
To back my mentioned interest:
I also wrote a UDF from a slightly shifted viewpoint, and applied it to a hypothetical case of simulated experimental statistics.
What I commented in the example file concerning “surprises” reminded me soehow of the situation where bankers are supposed to talk of leverage effects.
Probably this example may encourage the questioner to not ignore my comment above.
disask86283verySpecialDrawingFromaBallbox2.ods (55.3 KB)
Welcome @beermomosekuwa!
It’s strange to see you posting this question more than 40 minutes after it’s already been answered. It is not difficult for me to quote the decision again:
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)
Example - RandValueByCriteria.ods (15.0 KB)
Sorry… Actually my new account was on hold… I asked it yesterday… it just got approved now…Anyway, thank you very much 
What about a case where the 2D-ballot with 90 balls contains only one (or zero?) balls with a number in the accepted range?
It’s at the end of the function, here it is
If k<1 Then
getRandValue = "No matching values"
ElseIf k=1 Then
getRandValue = aTemp(k)
Else
getRandValue = aTemp(Rnd()*(k-1)+1)
EndIf
Sorry. I had misunderstood part of your code anyway. Must study more thoroughly next time.
It’s not difficult there. In principle, the same thing that you suggested: select from the original matrix and transfer to the auxiliary one-dimensional array only those values that match the selection criterion. Counter K contains the number of elements found. If nothing is found , then an error message. If only one value matches the selection criteria, then output that value. Otherwise, return the element with a random index from 1 to K.
1 Like
Yes thanks. Strange that I missed the point. Unreasonably I had assumed you actually “rolled dice” till one selected an accepted result (like the OP had worded the question). Of course, you didn’t. And the one how didn’t specifically account for the case of “no accepted value available” was I.
Anyway I would still like to see a realistic use-case for a “2D ball-box” and all that.