Spreadsheet Formulas for 18xx

Introduction

These are formulas that can be used (or adapted) to fully instrument a 18xx PBEM spreadsheet.
These formulas are taken from the 1870_V2.0 spreadsheet at
https://drive.google.com/open?id=1_MpVuwAR7MsgXzFxccUA_ZqSHvAb39gDgbAygWx2t38

The Top 8 Rows

Number of Players - cell A1

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

=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

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

Begin Worth - cells C3:C8

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

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