Spreadsheet Formulas for 18xx - Revision history

Rich: /* The INDIRECT Function */

2020-05-25T01:38:32Z

The INDIRECT Function

← Older revision		Revision as of 01:38, 25 May 2020
Line 125:		Line 125:

	<span style="color: Fuchsia; font-size: larger ">The Special Indirect Formulae (Submitted on Sun 10 May 2020)</span><br>		<span style="color: Fuchsia; font-size: larger ">The Special Indirect Formulae (Submitted on Sun 10 May 2020)</span><br>
	If the above snippet is modified to use a RowCol format and not a cell ref format it will look like this:		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>		<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.		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.

2020-05-25T01:37:26Z

The INDIRECT Function: added Special Indirect Formulae

← Older revision		Revision as of 01:37, 25 May 2020
Line 123:		Line 123:
	<pre>INDIRECT($A$14&"!AG3")</pre>		<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.		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 above snippet 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 ===		=== The ISERR and FIND functions ===

2018-03-25T15:19:57Z

Introduction

← Older revision		Revision as of 15:19, 25 March 2018
Line 7:		Line 7:
	such a spread sheet to make it both easy and intuitive to use.		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		I will use the formulas in the '''1870_V3.0''' spreadsheet at
	https://drive.google.com/open?id=~~1_MpVuwAR7MsgXzFxccUA_ZqSHvAb39gDgbAygWx2t38~~		https://drive.google.com/open?id=189j0ShSZgtut_cbPZWRJBKREvrhaVm1M_DlT9ekDw8I
	as examples in this document.		as examples in this document.

2017-09-25T23:05:39Z

An alternate formula for player count

← Older revision		Revision as of 23:05, 25 September 2017
Line 135:		Line 135:
	The player count formula above is one way to derive the number of players.<br>		The player count formula above is one way to derive the number of players.<br>
	The following formula is another way to accomplish this:		The following formula is another way to accomplish this:
	<pre>COUNTA(A3:A7)</pre>		<pre>COUNTA(A3:A8)</pre>
	Unlike the first version, this formula will return 1 if there is only one player entered.		Unlike the first version, this formula will return 1 if there is only one player entered.

2017-09-25T23:03:55Z

The ISERR and FIND functions: alternate formula for player count

← Older revision		Revision as of 23:03, 25 September 2017
Line 131:		Line 131:
	* The ISERR function tests if an error has occured and returns TRUE or FALSE.		* 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.		* 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:A7)</pre>
			Unlike the first version, this formula will return 1 if there is only one player entered.

2017-09-07T20:44:44Z

Dividends - row 33: expand

← Older revision		Revision as of 20:44, 7 September 2017
Line 113:		Line 113:

	=== Dividends - row 33 ===		=== 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>		<pre>=(E10+E11)*E24</pre>

2017-09-07T20:37:10Z

Private Income - row 32: expand

← Older revision		Revision as of 20:37, 7 September 2017
Line 107:		Line 107:

	=== Private Income - row 32 ===		=== 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)		<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>		+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 ===		=== Dividends - row 33 ===

2017-09-07T18:45:33Z

End Funds - row 23: expand

← Older revision		Revision as of 18:45, 7 September 2017
Line 103:		Line 103:

	=== End Funds - row 23 ===		=== 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>		<pre>=SUM(E20:E22)</pre>

2017-09-07T18:40:19Z

Cash Flow - row 22: expand

← Older revision		Revision as of 18:40, 7 September 2017
Line 99:		Line 99:

	=== Cash Flow - row 22 ===		=== 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>		<pre>=sum(E26:E30)+sum(E34:E37)</pre>

2017-09-07T18:36:39Z

Dividends - row 21: expand

← Older revision		Revision as of 18:36, 7 September 2017
Line 95:		Line 95:

	=== Dividends - row 21 ===		=== Dividends - row 21 ===
			This trivial formula merely adds the "Private Income" cell to the "Dividends" cell for that company.
	<pre>=E32+E33</pre>		<pre>=E32+E33</pre>