Formula to establish next smallest value

[deepheat101]

Hello

I have data from a game simulator that lists six available tokens with non-repeating values from 1 to 6 in any order.

The smallest value token is selected first (for an action that I have already constructed)

For each subsequent round of the game the next next smallest token is selected. It's easy enough for me to establish the selected token for that round using SMALL.

However, on each round none or one of the tokens may be removed by random; but for what it's worth a selected token can not be removed on that round. The "select" and "remove" are not related. For the next round the next smallest token of those remaining must be selected.

After six rounds or the largest item is selected and if there are any tokens left, then we start again from the smallest until either one token remains or ten rounds are played

The following example may assist..

A B C D E F
3 5 1 2 4 6   Round 1:  select 1
3 5 1 2 4 6   Round 2:  select 2
3 5 1 2 4 6   Round 3:  select 3 (Token 4 is randomly removed)
3 5 1 2   6   Round 4:  select 5 (Token 1 is randomly removed)
3 5   2   6   Round 5:  select 6
3 5   2   6   Round 6:  select 2 (Token 3 is randomly removed)
  5   2   6   Round 7:  select 5
  5   2   6   Round 8:  select 6 (Token 2 is randomly removed)
  5       6   Round 9:  select 5 (Token 6 is randomly removed)
  5           Round 10: end

Using SMALL to establish the selected token works upto the point until the next smallest value has already been removed..

What formula establishes which token is selected per round?

Many Thanks

dp

 

[sheetspread]

A-F are the six tokens? The numbers are the values? Doesn't look that way because you select 1 then 2 then 3 (which corresponds to 4???). Please explain further.

 

[deepheat101]

Many thanks for looking and replying

A-F are just the column references. The rows reflect the various rounds and the token values with the various random removals

    A B C D E F
 1. 3 5 1 2 4 6   Round  1:  smallest token value from row = 1 
 2. 3 5 1 2 4 6   Round  2:  next smallest = 2
 3. 3 5 1 2 4 6   Round  3:  next smallest = 3 (Token 4 is randomly removed)
 4. 3 5 1 2   6   Round  4:  next smallest = 5 (Token 1 is randomly removed)
 5. 3 5   2   6   Round  5:  next smallest = 6
 6. 3 5   2   6   Round  6:  next smallest = 2 (cycling back to start) (Token 3 is randomly removed)
 7.   5   2   6   Round  7:  next smallest = 5
 8.   5   2   6   Round  8:  next smallest = 6 (Token 2 is randomly removed)
 9.   5       6   Round  9:  next smallest = 5 (cycling back to start again) (Token 6 is randomly removed)
10.   5           Round 10:  end

To start with I've experimented combining SMALL along with MOD to control the cycling with the pertinent rounds, which works as long as no token values are removed. While this gives the next smallest token for each subsquent rount, it fails when even a single a token is removed.

 

[sheetspread]

How do you know when to remove the tokens? Is it a 50/50 decision at each round?

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H
1	3	5	1	2	4	6		1
2	3	5	1	2	4	6		2
3	3	5	1	2	4	6		3
4	3	5	1	2		6		5
5	3	5		2		6		6
6	3	5		2		6		2
7		5		2		6		5
8		5		2		6		6
9		5				6		5
10		5
Sheet1

Cell Formulas
Range	Formula
H1	=MIN(A1:F1)
H2	{=IF(COUNTA(A2:F2)>1,IFERROR(1/(1/MIN(IF(A2:F2>H1,A2:F2))),MIN(A2:F2)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy H2 formula down as necessary.

WBD

 

[deepheat101]

How do you know when to remove the tokens? Is it a 50/50 decision at each round?

Yes. And any of the remaining tokens can be removed in that round apart from the one that was selected during that round.

 

[deepheat101]

WBD - I love you and I want to have your babies.

That works wonderfully well. I've struggled with one for a couple of days and I would never have derived the formula that you have supplied. Many Thanks

To aid my future understanding, can you please explain how this works?

 

[wideboydixon]

=IF(COUNTA(A2:F2)>1,IFERROR(1/(1/MIN(IF(A2:F2>H1,A2:F2))),MIN(A2:F2)),"")

First look across the row. If there aren't at least two values then return an empty string.
If there are at least two values then find the minimum value that is greater than the cell above (H1 in this case). This could return zero (if the value above is 6 for example) so the 1/(1/()) part will generate an error in this case (division by zero), forcing it to revert to finding the minimum value again.

Hope that makes sense. I'll be coming round with my Barry White CD later ...

WBD