Spreadsheet Formulas for 18xx

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 such 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_V2.0 spreadsheet at
https://drive.google.com/open?id=1_MpVuwAR7MsgXzFxccUA_ZqSHvAb39gDgbAygWx2t38
as examples in the discussion below.

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. The following formulas are used to do this.

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.

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.

Max Certs - cell AB2

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

Certs - cells AB3:AB8

=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))

=== Shares - cells AC3:AC8 ===
=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

Dividends - cells AD3:AD8

=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

End Cash - cells AF3:AF8

=B3+D3+AD3+AE3

End Worth - cells AG3:AG8

=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

The Rest of the Sheet

Shares in IPO - row 11

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

Begin Funds - row 20

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

Dividends - row 21

=E32+E33

Cash Flow - row 22

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

End Funds - row 23

=SUM(E20:E22)

Private Income - row 32

=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)

Dividends - row 33

=(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.