@ > Home > Contents > Multiple SUBSTITUTE(): The Paper-Stone-Scissors-Relation Excel 4+

The Game

You know the intransitive relation: Paper wraps stone, stone destroys scissors, scissors cuts paper.

Player1 chooses one of the 3 in A1, Player2 in B1. A1:B1 are Data Validity restricted to the 3 words (not capitalized). The winner is:

Normal formula: ="Player"&MOD(LEN(SUBSTITUTE(SUBSTITUTE(B1,"s",),"n",))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"s",),"n",)),3)
Array formula: {="Player"&MOD(SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:B1,"s",),"n",))*{-1\1}),3)}

What happened here?

You need 3 strings of different, but neighbored length. Our 3 words have the lengths 5, 5, 8. If "s" and "n" are omitted, we get 5, 3, 4 ("paper, toe, cior"). There is no necessary order because you have only 2 orders possible. This can be managed by order of subtraction. MOD finally gets you to the round point of intransitivity.

Finally discovered (on 2006/12/01)

Normally you have to use 2 nested SUBSTITUTE()-functions. Very odd: It also works as follows:

Array formula: {="Player"&MOD(SUM(LEN(SUBSTITUTE(A1:B1,{"s","n"},))*{-1\1}),3)}

Multiple SUBSTITUTE(): Anywhere else? - True is: It is not a multiple SUBSTITUTE but a 2-by-2-entries LEN {SUM}. In this special case you can "force" SUBSTITUTE to work twice while formulated once.

Add a well (on 2006/12/02)

"Scissors and Stone are going to hell, but Paper will cover the deepest Well"

Array formula: {="Player"&CHOOSE(SUM(MATCH(LEFT(A1:B1,2),{"pa","we","st","sc"},)*{-1\1})+4,1,2,2,0,1,1,2)}

In general, you would replace p=>1, w=>2, st=>3, sc=>4, to enable shorter formulas and for easier understanding at all: "only 4 is ranking above 1, all other ranks are normal".

Normal formula: ="Player"&CHOOSE(B1-A1+4,1,2,2,0,1,1,2)

|v    v|                          /
| \  / |       --->--St-->-Sc-->-P- etc.
^  \/  v      /     /     /
|  /\  |     /     ^     /
| /  \ |    /     /     /
Sc--<-St   P-->--W-->---

Charts: 6 instead of 3 relations - Order of dominance