How to force recalculation based on win/loss ratio

[tdbarnard]

Column A contains 150 rows of randomly selected values of either +15 or -10.
Cell $B$2 specifies what the minimum ratio is of wins (+15) vs losses (-10).
Cell $C$3 calculates the actual ratio of wins vs losses.
The cells of column A have the following equation:
=IF(RANDBETWEEN(0,100)<=$B$2,-10,+15)
Cell $C$2 is set to the following equation:
=(COUNTIF(A6:A146,">0")/150)*100

What I am trying to figure out is how to force the spreadsheet to compare $C$2 against $B$2 and so long as $C$2 is > $B$2 to continue to repeat recalculating the sheet and only stop once $C$2 <= $B$2.
$B$2 can be set to a value of 30 through 100.

In general, is there a way to force a spreadsheet to keep recalculating until a certain cell vs cell condition is met?

[JohnSUN]

to continue to repeat recalculating the sheet

In general, your question seems to be quite clear - how to repeat the calculations until the condition is met. And this can be done easily with Tools - Goal Seek. There are questions about the correspondence between “count 150” and “range in COUNTIF A6: A146”, but this could be a typo. The main question that prompts your description is the quoted part. Do you want to “recalculate the table” - that is, the entire set of these RANDBETWEENs until the Сount(+15) is a predetermined percentage? If so, then you have chosen not the fastest way to create this table.

[tdbarnard]

I know the basics of creating spreadsheets, but I don’t know them well enough to know if there is a faster/better way to do what I’m trying to do. Could you elaborate?
Regarding the range, yes it was a typo. The range should’ve been A6:A156.

[JohnSUN]

No, A6:A156 is 151 values. But it is not important. Before showing how to do this quickly, I want to clarify one detail. Did I understand correctly that B2 records the percentage of wins (+15) that you want to see in A6:A155 in random order? If so, how many winnings would you like to have if, for example, 85 is entered in B2? This is 56.67 for 150 values - do you want 56? Or 57?

[tdbarnard]

B2 is where you enter the percentage of wins you want to see. C2 calculates what the sheet recalculation’s actual percentage of wins resulted in. (Basic idea is in B2 you enter the percentage of wins you want to see.) 56.67 would round up to 57.
Thank you for the help!

[JohnSUN]

Place the formula in cell A6 and stretch it to cell A155

=IF(COUNTIF($A$5:A5;15)>=ROUNDUP($B$2*150/100);-10;
      IF(COUNTIF($A$5:A5;-10)>=(150-ROUNDUP($B$2*150/100));15;
      IF(RAND()<$B$2/100;15;-10)))

Each press of F9 or Ctrl+Shit+F9 (like any other change on the sheet) will give a new combination of wins and losses.

A few words to explain the formula.
This part of the formula IF(RAND()<$B$2/100;15;-10) does the main work. RAND() tries more or less successfully to generate random numbers from 0 to 1. This number can be regarded as a percentage. And in cell B2, judging by your description, there is the desired percentage of victories. It is enough to divide the number from B2 by 100 and these values can be compared. The IF() function does exactly that - if the generated random number is included in the percentage of wins, then the result is “Win”, otherwise “Loss”.

However, if you use only this generator, the result may differ from the desired by several numbers.

Therefore, the “generator” is wrapped in two more functions IF() - “if the required number of wins has already been generated, then the LOSS” and vice versa “if the required number of losses has already been generated, then WIN”

The number of already generated WINS is calculated using COUNTIF(A$5:A5;15) - this is the number of WINS in all cells above the current one. When stretching the formula, the second part of the range will automatically increase - A$5:A6, A$5:A7, and so on - because it is specified by a relative reference, without a dollar sign in front of the row number. The number of losses is calculated in the same way, only the second parameter in the function is not 15, but -10

The required number of wins is calculated as ROUNDUP($B$2*150/100), and the number of losses is (150-ROUNDUP($B$2*150/100)).

In order not to calculate the required number of wins and losses each time, you can calculate them separately. For example, put in cell C2 the formula =ROUNDUP(B2*150/100), and in cell D2 =150-C2. And instead of the usual RAND() function, you can use the new RAND.NV() function

Now the generator formula can be written a little shorter

=IF(COUNTIF(A$5:A5;15)>=$C$2;-10;
      IF(COUNTIF(A$5:A5;-10)>=$D$2;15;
          IF(RAND.NV()<$B$2/100;15;-10)))

[tdbarnard]

Thanks for the help; your solution worked great!