'Recalculate Google Sheets until criteria is met
I have a sheet and I want to recalculate the formulas until the criteria is met. I am supplying this demo sheet to show what I want to achieve.
I have a list in Col A. Im picking 10 random from the list in B. Checking Unique in C. Counting number of Unique values in D.
I have a condition set in E1 which test the D1 value. Above 5 is good. Under 5 is bad and requires a refresh to recalculate the formulas to obtain a new random list till it goes above 5.
Any suggestion how can I achieve this please?
I thank you in advance for your guidance.
NOTE: I am not looking for solutions to generate X number of random values from the list. I want to know if it is possible to implement like a 'while loop' in google sheets. Recalculate formulas until the condition is fulfilled then stop.
Solution 1:[1]
use:
=QUERY(UNIQUE(SORT(A2:A, RANDARRAY(ROWS(A2:A)), 1)),
"where Col1 is not null limit 10", )
if you want "above 5" use:
=QUERY(UNIQUE(SORT(A2:A, RANDARRAY(ROWS(A2:A)), 1)),
"where Col1 is not null limit 6", )
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | player0 |