Difference between revisions of "Spreadsheet Formulas for 18xx"
(initial contents) |
(→Introduction: revised) |
||
| Line 1: | Line 1: | ||
== Introduction == | == 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 | |||
https://drive.google.com/open?id=1_MpVuwAR7MsgXzFxccUA_ZqSHvAb39gDgbAygWx2t38 | been written by many different people. This is because each 18xx game requires its own such spreadsheet. | ||
This document is a first attempt to stadardize 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> | |||
https://drive.google.com/open?id=1_MpVuwAR7MsgXzFxccUA_ZqSHvAb39gDgbAygWx2t38 <br> | |||
as examples in the discussion below. | |||
== The Top 8 Rows == | == The Top 8 Rows == | ||
Revision as of 13:13, 1 September 2017
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 a first attempt to stadardize 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
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
=(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