Difference between revisions of "Spreadsheet Formulas for 18xx"

Latest revision as of 18:38, 24 May 2020

Introduction

18xx PBEM games that use BOARD18 to display the game board and stock chart also require a spreadsheet to track the financials of the game. There are many such spreadsheets that have been written by many different people. This is because each 18xx game requires its own spreadsheet. This document is my first attempt to standardize the approach used in creating these spreadsheets. I will attempt to show how to fully automate such a spread sheet to make it both easy and intuitive to use.

I will use the formulas in the 1870_V3.0 spreadsheet at https://drive.google.com/open?id=189j0ShSZgtut_cbPZWRJBKREvrhaVm1M_DlT9ekDw8I as examples in this document.

The Top 8 Rows

The top 8 rows of this spreadsheet are used to track the position of each player in the game. The first two rows contain heading information. Then there is a row for each player. That row shows the player's cash position, share holdings and net worth in the game. The formulas described below are used to do this. The first three formulas appear only once. They are in the heading rows. The rest of the formulas appear in sets of six, once in each player row. The six formulas in each set are slightly different because they refer to the values for the player described in that row. My examples use the version of each set that appears in row 3, the row for player 1.

Number of Players - cell A1

The formula in cell A1 computes the number of players by looking for a blank player name in column A:

=IF(ISBLANK(A8),IF(ISBLANK(A7),IF(ISBLANK(A6),IF(ISBLANK(A5),2,3),4),5),6)

The formula assumes that the minimum number of players is 2 and the maximum is 6.

Max Certs - cell AB2

The value in the "Max Certs" cell is looked up in the third row of the table at AF10. This table is indexed by the number of players, in cell A1.

=INDEX($AH$11:$AH$16,$A$1)

Number of Players - cell AH1

This trivial formula merely copies the value in cell A1.

Begin Cash - cells B3:B8

The values in the "Begin Cash" cells are determined differently for the first spread sheet tab than they are determined for all subsequent tabs. Cell A14 is used to determine if this is the first tab. Cell A14 should contain the name of the previous tab for all subsequent tabs, but it is blank on the first tab. In addition the "Begin Cash" cells are always blank for all rows that have a blank player name in column A.

=IF(ISBLANK($A3),"",IF(ISBLANK($A$14),INDEX($AG$11:$AG$16,$A$1),INDIRECT($A$14&"!AF3")))

On the first spread sheet tab the value in all of the "Begin Cash" cells is looked up in the second row of the table at AF10. This table is indexed by the number of players which is in cell A1.

On subsequent spread sheet tabs the value in each of the "Begin Cash" cells is copied from the corresponding "End Cash" cell on the previous tab. See the note on the INDIRECT function for the details of this trick.

Begin Worth - cells C3:C8

The values in the "Begin Worth" cells are determined differently for the first spread sheet tab than they are determined for all subsequent tabs. Cell A14 is used to determine if this is the first tab. Cell A14 should contain the name of the previous tab for all subsequent tabs, but it is blank on the first tab. In addition the "Begin Worth" cells are always blank for all rows that have a blank player name in column A.

=IF(ISBLANK($A3),"",IF(ISBLANK($A$14),INDEX($AG$11:$AG$16,$A$1),INDIRECT($A$14&"!AG3")))

On the first spread sheet tab the value in all of the "Begin Worth" cells is looked up in the second row of the table at AF10. This table is indexed by the number of players which is in cell A1.

On subsequent spread sheet tabs the value in each of the "Begin Worth" cells is copied from the corresponding "End Worth" cell on the previous tab. See the note on the INDIRECT function for the details of this trick.

Certs - cells AB3:AB8

The values in the "Certs" cells are computed using this intimidating looking formula. The number of certificates that a player owns consists of a count of the private companies owned added to a count of the stock certificates owned. This is complicated by the fact that president certificates represent 2 shares and only shares are counted in the column for each company. This formula is better analyzed by taking it in parts.

=IF(ISERR(FIND("1",Y3)),0,1)+IF(ISERR(FIND("2",Y3)),0,1)+IF(ISERR(FIND("3",Y3)),0,1)+
IF(ISERR(FIND("4",Y3)),0,1)+IF(ISERR(FIND("6",Y3)),0,1)+IF(E$14="y",0,IF(E3="X",F3-1,F3))+
IF(G$14="y",0,IF(G3="X",H3-1,H3))+IF(I$14="y",0,IF(I3="X",J3-1,J3))+IF(K$14="y",0,IF(K3="X",L3-1,L3))+
IF(M$14="y",0,IF(M3="X",N3-1,N3))+IF(O$14="y",0,IF(O3="X",P3-1,P3))+IF(Q$14="y",0,IF(Q3="X",R3-1,R3))+
IF(S$14="y",0,IF(S3="X",T3-1,T3))+IF(U$14="y",0,IF(U3="X",V3-1,V3))+IF(W$14="y",0,IF(W3="X",X3-1,X3))

In the first half of this formula a code snippet is repeated five times, once for each private company certificate. The snippet is shown here:

IF(ISERR(FIND("1",Y3)),0,1)+

See the note on the ISERR and FIND functions for the details of this snippet.

In the second half of this formula a code snippet is repeated ten times, once for each major company. The snippet is shown here:

IF(E$14="y",0,IF(E3="X",F3-1,F3))+

The first IF function determines if the price of this company is in the yellow (or green or brown) area of the stock chart. If it is then these certificates are not counted here. The second IF function adjusts the count if this player is the president of the company.

Shares - cells AC3:AC8

The values in the "Shares" cells are computed using this less intimidating formula. The number of shares that a player owns consists of a count of the private companies owned added to a count of the stock shares owned. The first half of this formula is identical to the first half of the "Certs" formula above. The second half is merely a count of the major company shares owned.

=IF(ISERR(FIND("1",Y3)),0,1)+IF(ISERR(FIND("2",Y3)),0,1)+IF(ISERR(FIND("3",Y3)),0,1)+
IF(ISERR(FIND("4",Y3)),0,1)+IF(ISERR(FIND("6",Y3)),0,1)+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3

You can see the note on the ISERR and FIND functions for more details.

Dividends - cells AD3:AD8

The "Dividends" formula is a variant of the "Shares" formula above. It accesses some different cells but the logic is the same.

=IF(ISERR(FIND("1",Z3)),0,5)+IF(ISERR(FIND("2",Z3)),0,10)+IF(ISERR(FIND("3",Z3)),0,10)+
IF(ISERR(FIND("4",Z3)),0,15)+IF(ISERR(FIND("6",Z3)),0,20)+
F3*E$24+H3*G$24+J3*I$24+L3*K$24+N3*M$24+P3*O$24+R3*Q$24+T3*S$24+V3*U$24+X3*W$24

Again, you can see the note on the ISERR and FIND functions for more details.

End Cash - cells AF3:AF8

This trivial formula merely adds up the 4 cells that track cash for this player.

=B3+D3+AD3+AE3

End Worth - cells AG3:AG8

The "End Worth" formula is another variant of the "Shares" formula above. It accesses some different cells but the logic is the same.

=IF(ISERR(FIND("1",Y3)),0,20)+IF(ISERR(FIND("2",Y3)),0,40)+IF(ISERR(FIND("3",Y3)),0,50)+
IF(ISERR(FIND("4",Y3)),0,80)+IF(ISERR(FIND("6",Y3)),0,160)+
AF3+F3*F$13+H3*H$13+J3*J$13+L3*L$13+N3*N$13+P3*P$13+R3*R$13+T3*T$13+V3*V$13+X3*X$13

Yet again, you can see the note on the ISERR and FIND functions for more details.

Player - cells AH3:AH8

This trivial formula merely copies the player names from cells A3:A8.

The Rest of the Sheet

Cells E9 through X37 are used to hold information about the ten major companies. Two columns of this area are dedicated to each of the 10 major companies. Those two columns are used to track the company's share holdings, price, privates owned, trains owned, remaining tokens, and cash position. The formulas described below are used to do this. These formulas appear in sets of ten, one for each company. The ten formulas in each set are slightly different because they refer to the values for the company described in that column. My examples use the version of each set that appears in column E, the column for the ATSF.

Shares in IPO - row 11

Every major company has a total of 10 shares. Any shares that are not either in players hands (rows 3 through 8) or retained by the company (row 9) or in the market (row 10) must be in the IPO. This formula calculates that amount.

=(10-SUM(F3:F8))-E9-E10

Begin Funds - row 20

The values in the "Begin Funds" cells are determined differently for the first spread sheet tab than they are determined for all subsequent tabs. Cell A14 is used to determine if this is the first tab. Cell A14 should contain the name of the previous tab for all subsequent tabs, but it is blank on the first tab.

=IF(ISBLANK($A$14),0,INDIRECT($A$14&"!E23"))

On the first spread sheet tab the value in all of the "Begin Funds" cells is 0. On subsequent spread sheet tabs the value in each of the "Begin Funds" cells is copied from the corresponding "End Funds" cell on the previous tab. See the note on the INDIRECT function for the details of this trick.

Dividends - row 21

This trivial formula merely adds the "Private Income" cell to the "Dividends" cell for that company.

=E32+E33

Cash Flow - row 22

This formula adds up all of the cells that effect the cash position of the company except for the dividends.

=sum(E26:E30)+sum(E34:E37)

End Funds - row 23

This formula adds together the "Begin Funds" cell, the "Dividends" cell, and the "Cash Flow" cell for that company.

=SUM(E20:E22)

Private Income - row 32

The "Private Income" formula is yet another variant of the "Shares" formula above. It accesses different cells but the logic is the same. The idea this time is to add up the income generated by each private company that this major company owns.

=IF(ISERR(FIND("1",E16)),0,5)+IF(ISERR(FIND("2",E16)),0,10)+IF(ISERR(FIND("3",E16)),0,10)
+IF(ISERR(FIND("4",E16)),0,15)+IF(ISERR(FIND("6",E16)),0,20)

You can see the note on the ISERR and FIND functions for more details.

Dividends - row 33

The dividend per share paid, if any, can be found in row 24. Any shares listed in rows 10 and 11 will pay dividends to this company.

=(E10+E11)*E24

Tricks and Tips

Some programming tricks are used repeatedly in a lot of the above formulas. These things are documented in detail here.

The INDIRECT Function

This is one very useful trick that is used a lot in the above formulas. It is a way to refer to a cell on a different spread sheet tab. But more it is a way to dynamically control which spread sheet tab is actually used. To do this you must reserve a cell on the current spreadsheet tab to hold the name of tab that contains the referenced cell. In the example below this reserved cell is A14 and the cell on the referenced tab that is being used is cell AG3.

INDIRECT($A$14&"!AG3")

Note that the string "!AG3" starts and ends with quotation marks. This, of course, means that this string is a literal. As such it will not be automatically adjusted by a copy/paste action! Be careful.

The Special Indirect Formulae (Submitted on Sun 10 May 2020)
If the part of the above snippet in quotes is modified to use a RowCol format and not a cell ref format it will look like this:

INDIRECT($A$14&"!RC[-31]")

The R means use the current row and the C[x] means use the current column plus or minus an offset. The general form of the RowCol format is R[x]C[y] so offsets can be applied to either the row or the column. The advantage of the RowCol format in this exact case is that the part of the formula in the quotes is the same in every row. This can save a lot of typing.

The ISERR and FIND functions

This trick can be used to look for a given character in the text contained in a cell:

IF(ISERR(FIND("1",Y3)),0,1)

Taking things from the center out:

The FIND function looks for the first value ("1") in the cell Y3. It returns an error if this value is not found.
The ISERR function tests if an error has occured and returns TRUE or FALSE.
The IF function returns one of two values dependin on the result returned by ISERR.

An alternate formula for player count

The player count formula above is one way to derive the number of players.
The following formula is another way to accomplish this:

COUNTA(A3:A8)

Unlike the first version, this formula will return 1 if there is only one player entered.

@@ Line 2: / Line 2: @@
 xx PBEM games that use BOARD18 to display the game board and stock chart also require a
 spreadsheet to track the financials of the game. There are many such spreadsheets that have
-been written by many different people. This is because each 18xx game requires its own such spreadsheet.
+been written by many different people. This is because each 18xx game requires its own spreadsheet.
-This document is a first attempt to stadardize the
+This document is my first attempt to standardize the
 approach used in creating these spreadsheets. I will attempt to show how to fully automate
 such a spread sheet to make it both easy and intuitive to use.
-I will use the formulas in the '''1870_V2.0''' spreadsheet at <br>
+I will use the formulas in the '''1870_V3.0''' spreadsheet at
-https://drive.google.com/open?id=1_MpVuwAR7MsgXzFxccUA_ZqSHvAb39gDgbAygWx2t38 <br>
+https://drive.google.com/open?id=189j0ShSZgtut_cbPZWRJBKREvrhaVm1M_DlT9ekDw8I
-as examples in the discussion below.
+as examples in this document.
 == The Top 8 Rows ==
-The top 8 rows of this spreadsheet are used to track the position of each player in the game. There is  a row for each player and that row shows the player's cash position, share holdings and net worth in the game. These formulas are used to do this.
+The top 8 rows of this spreadsheet are used to track the position of each player in the game. The first two rows contain heading information. Then there is  a row for each player. That row shows the player's cash position, share holdings and net worth in the game. The formulas described below are used to do this. The first three formulas appear only once. They are in the heading rows. The rest of the formulas appear in sets of six, once in each player row. The six formulas in each set are slightly different because they refer to the values for the player described in '''that''' row. My examples use the version of each set that appears in row 3, the row for player 1.
 === Number of Players - cell A1 ===
-This formula in cell A1 computes the number of players by looking for a blank player name:
+The formula in cell A1 computes the number of players by looking for a blank player name in column A:
 <pre>=IF(ISBLANK(A8),IF(ISBLANK(A7),IF(ISBLANK(A6),IF(ISBLANK(A5),2,3),4),5),6)</pre>
 The formula assumes that the minimum number of players is 2 and the maximum is 6.
+=== Max Certs - cell AB2 ===
+The value in the "Max Certs" cell is looked up in the third row of the table at AF10. This table is indexed by the number of players, in cell A1.
+<pre>=INDEX($AH$11:$AH$16,$A$1)</pre>
+=== Number of Players - cell AH1 ===
+This trivial formula merely copies the value in cell A1.
 === Begin Cash - cells B3:B8 ===
+The values in the "Begin Cash" cells are determined differently for the first spread sheet tab than they are determined for all subsequent tabs. Cell A14 is used to determine if this is the first tab. Cell A14 should contain the name of the previous tab for all subsequent tabs, but it is blank on the first tab. In addition the "Begin Cash" cells are always blank for all rows that have a blank player name in column A.
 <pre>=IF(ISBLANK($A3),"",IF(ISBLANK($A$14),INDEX($AG$11:$AG$16,$A$1),INDIRECT($A$14&"!AF3")))</pre>
+On the first spread sheet tab the value in all of the "Begin Cash" cells is looked up in the second row of the table at AF10. This table is indexed by the number of players which is in cell A1.
+On subsequent spread sheet tabs the value in each of the "Begin Cash" cells is copied from the corresponding "End Cash" cell on the previous tab. See the note on [[Spreadsheet_Formulas_for_18xx#The_INDIRECT_Function|the INDIRECT function]] for the details of this trick.
 === Begin Worth - cells C3:C8 ===
+The values in the "Begin Worth" cells are determined differently for the first spread sheet tab than they are determined for all subsequent tabs. Cell A14 is used to determine if this is the first tab. Cell A14 should contain the name of the previous tab for all subsequent tabs, but it is blank on the first tab. In addition the "Begin Worth" cells are always blank for all rows that have a blank player name in column A.
 <pre>=IF(ISBLANK($A3),"",IF(ISBLANK($A$14),INDEX($AG$11:$AG$16,$A$1),INDIRECT($A$14&"!AG3")))</pre>
+On the first spread sheet tab the value in all of the "Begin Worth" cells is looked up in the second row of the table at AF10. This table is indexed by the number of players which is in cell A1.
-=== Max Certs - cell AB2 ===
+On subsequent spread sheet tabs the value in each of the "Begin Worth" cells is copied from the corresponding "End Worth" cell on the previous tab. See the note on [[Spreadsheet_Formulas_for_18xx#The_INDIRECT_Function|the INDIRECT function]] for the details of this trick.
-<pre>=INDEX($AH$11:$AH$16,$A$1)</pre>
 === Certs - cells AB3:AB8 ===
+The values in the "Certs" cells are computed using this intimidating looking formula. The number of certificates that a player owns consists of a count of the private companies owned added to a count of the stock certificates owned. This is complicated by the fact that president certificates represent 2 shares and only shares are counted in the column for each company. This formula is better analyzed by taking it in parts.
 <pre>=IF(ISERR(FIND("1",Y3)),0,1)+IF(ISERR(FIND("2",Y3)),0,1)+IF(ISERR(FIND("3",Y3)),0,1)+
 IF(ISERR(FIND("4",Y3)),0,1)+IF(ISERR(FIND("6",Y3)),0,1)+IF(E$14="y",0,IF(E3="X",F3-1,F3))+
@@ Line 34: / Line 47: @@
 IF(M$14="y",0,IF(M3="X",N3-1,N3))+IF(O$14="y",0,IF(O3="X",P3-1,P3))+IF(Q$14="y",0,IF(Q3="X",R3-1,R3))+
 IF(S$14="y",0,IF(S3="X",T3-1,T3))+IF(U$14="y",0,IF(U3="X",V3-1,V3))+IF(W$14="y",0,IF(W3="X",X3-1,X3))</pre>
+In the first half of this formula a code snippet is repeated five times, once for each private company certificate. The snippet is shown here:
+<pre>IF(ISERR(FIND("1",Y3)),0,1)+</pre>
+See the note on [[Spreadsheet_Formulas_for_18xx#The_ISERR_and_FIND_functions|the ISERR and FIND functions]] for the details of this snippet.
-<pre>=== Shares - cells AC3:AC8 ===
+In the second half of this formula a code snippet is repeated ten times, once for each major company. The snippet is shown here:
-=IF(ISERR(FIND("1",Y3)),0,1)+IF(ISERR(FIND("2",Y3)),0,1)+IF(ISERR(FIND("3",Y3)),0,1)+
+<pre>IF(E$14="y",0,IF(E3="X",F3-1,F3))+</pre>
+The first IF function determines if the price of this company is in the yellow (or green or brown) area of the stock chart. If it is then these certificates are not counted here. The second IF function adjusts the count if this player is the president of the company.
+=== Shares - cells AC3:AC8 ===
+The values in the "Shares" cells are computed using this less intimidating formula. The number of shares that a player owns consists of a count of the private companies owned added to a count of the stock shares owned. The first half of this formula is identical to the first half of the "Certs" formula above. The second half is merely a count of the major company shares owned.
+<pre>=IF(ISERR(FIND("1",Y3)),0,1)+IF(ISERR(FIND("2",Y3)),0,1)+IF(ISERR(FIND("3",Y3)),0,1)+
 IF(ISERR(FIND("4",Y3)),0,1)+IF(ISERR(FIND("6",Y3)),0,1)+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3</pre>
+You can see the note on [[Spreadsheet_Formulas_for_18xx#The_ISERR_and_FIND_functions|the ISERR and FIND functions]] for more details.
 === Dividends - cells AD3:AD8 ===
+The "Dividends" formula is a variant of the "Shares" formula above. It accesses some different cells but the logic is the same.
 <pre>=IF(ISERR(FIND("1",Z3)),0,5)+IF(ISERR(FIND("2",Z3)),0,10)+IF(ISERR(FIND("3",Z3)),0,10)+
 IF(ISERR(FIND("4",Z3)),0,15)+IF(ISERR(FIND("6",Z3)),0,20)+
 F3*E$24+H3*G$24+J3*I$24+L3*K$24+N3*M$24+P3*O$24+R3*Q$24+T3*S$24+V3*U$24+X3*W$24</pre>
+Again, you can see the note on [[Spreadsheet_Formulas_for_18xx#The_ISERR_and_FIND_functions|the ISERR and FIND functions]] for more details.
 === End Cash - cells AF3:AF8 ===
+This trivial formula merely adds up the 4 cells that track cash for this player.
 <pre>=B3+D3+AD3+AE3</pre>
 === End Worth - cells AG3:AG8 ===
+The "End Worth" formula is another variant of the "Shares" formula above. It accesses some different cells but the logic is the same.
 <pre>=IF(ISERR(FIND("1",Y3)),0,20)+IF(ISERR(FIND("2",Y3)),0,40)+IF(ISERR(FIND("3",Y3)),0,50)+
 IF(ISERR(FIND("4",Y3)),0,80)+IF(ISERR(FIND("6",Y3)),0,160)+
 AF3+F3*F$13+H3*H$13+J3*J$13+L3*L$13+N3*N$13+P3*P$13+R3*R$13+T3*T$13+V3*V$13+X3*X$13</pre>
+Yet again, you can see the note on [[Spreadsheet_Formulas_for_18xx#The_ISERR_and_FIND_functions|the ISERR and FIND functions]] for more details.
+=== Player - cells AH3:AH8 ===
+This trivial formula merely copies the player names from cells A3:A8.
 == The Rest of the Sheet ==
+Cells E9 through X37 are used to hold information about the ten major companies. Two columns of this area are dedicated to each of the 10 major companies. Those two columns are used to track the company's share holdings, price, privates owned, trains owned, remaining tokens, and cash position. The formulas described below are used to do this. These formulas appear in sets of ten, one for each company. The ten formulas in each set are slightly different because they refer to the values for the company described in that column. My examples use the version of each set that appears in column E, the column for the ATSF.
 === Shares in IPO - row 11 ===
-<pre>=(10-SUM(F4:F8))-E9-E10</pre>
+Every major company has a total of 10 shares. Any shares that are not either in players hands (rows 3 through 8) or retained by the company (row 9) or in the market (row 10) must be in the IPO. This formula calculates that amount.
+<pre>=(10-SUM(F3:F8))-E9-E10</pre>
 === Begin Funds - row 20 ===
+The values in the "Begin Funds" cells are determined differently for the first spread sheet tab than they are determined for all subsequent tabs. Cell A14 is used to determine if this is the first tab. Cell A14 should contain the name of the previous tab for all subsequent tabs, but it is blank on the first tab.
 <pre>=IF(ISBLANK($A$14),0,INDIRECT($A$14&"!E23"))</pre>
+On the first spread sheet tab the value in all of the "Begin Funds" cells is 0.
+On subsequent spread sheet tabs the value in each of the "Begin Funds" cells is copied from the corresponding "End Funds" cell on the previous tab. See the note on [[Spreadsheet_Formulas_for_18xx#The_INDIRECT_Function|the INDIRECT function]] for the details of this trick.
 === Dividends - row 21 ===
+This trivial formula merely adds the "Private Income" cell to the "Dividends" cell for that company.
 <pre>=E32+E33</pre>
 === Cash Flow - row 22 ===
+This formula adds up all of the cells that effect the cash position of the company except for the dividends.
 <pre>=sum(E26:E30)+sum(E34:E37)</pre>
 === End Funds - row 23 ===
+This formula adds together the "Begin Funds" cell, the "Dividends" cell, and the "Cash Flow" cell for that company.
 <pre>=SUM(E20:E22)</pre>
 === Private Income - row 32 ===
+The "Private Income" formula is yet another variant of the "Shares" formula above. It accesses different cells but the logic is the same. The idea this time is to add up the income generated by each private company that this major company owns.
 <pre>=IF(ISERR(FIND("1",E16)),0,5)+IF(ISERR(FIND("2",E16)),0,10)+IF(ISERR(FIND("3",E16)),0,10)
 +IF(ISERR(FIND("4",E16)),0,15)+IF(ISERR(FIND("6",E16)),0,20)</pre>
+You can see the note on [[Spreadsheet_Formulas_for_18xx#The_ISERR_and_FIND_functions|the ISERR and FIND functions]] for more details.
 === Dividends - row 33 ===
+The dividend per share paid, if any, can be found in row 24. Any shares listed in rows 10 and 11 will pay dividends to this company.
 <pre>=(E10+E11)*E24</pre>
+== Tricks and Tips ==
+Some programming tricks are used repeatedly in a lot of the above formulas. These things are documented in detail here.
+=== The INDIRECT Function ===
+This is one very useful trick that is used a lot in the above formulas. It is a way to refer to a cell on a different spread sheet tab. But more it is a way to dynamically control which spread sheet tab is actually used. To do this you must reserve a cell on the current spreadsheet tab to hold the name of tab that contains the referenced cell. In the example below this reserved cell is A14 and the cell on the referenced tab that is being used is cell AG3.
+<pre>INDIRECT($A$14&"!AG3")</pre>
+Note that the string "!AG3" starts and ends with quotation marks. This, of course, means that this string is a literal. As such it will '''not''' be automatically adjusted by a copy/paste action! Be careful.
+<span style="color: Fuchsia; font-size: larger ">The Special Indirect Formulae (Submitted on Sun 10 May 2020)</span><br>
+If the part of the above snippet in quotes is modified to use a RowCol format and not a cell ref format it will look like this:
+<pre>INDIRECT($A$14&"!RC[-31]")</pre>
+The '''R''' means use the current row and the '''C[x]''' means use the current column plus or minus an offset. The general form of the RowCol format is '''R[x]C[y]''' so offsets can be applied to either the row or the column. The advantage of the RowCol format in this exact case is that the part of the formula in the quotes is the same in every row.
+This can save a lot of typing.
+=== The ISERR and FIND functions ===
+This trick can be used to look for a given character in the text contained in a cell:
+<pre>IF(ISERR(FIND("1",Y3)),0,1)</pre>
+Taking things from the center out:
+* The FIND function looks for the first value ("1") in the cell Y3. It returns an error if this value is not found.
+* The ISERR function tests if an error has occured and returns TRUE or FALSE.
+* The IF function returns one of two values dependin on the result returned by ISERR.
+=== An alternate formula for player count ===
+The player count formula above is one way to derive the number of players.<br>
+The following formula is another way to accomplish this:
+<pre>COUNTA(A3:A8)</pre>
+Unlike the first version, this formula will return 1 if there is only one player entered.