My approach to setting up the economy of the game: spreadsheets
This post was written in response to Brenda Romero and Jan Schreiber’s suggestion that developers share their favorite Excel workflow methods.
I have completed quite a lot of projects on the economics of games using Excel. Today, when configuring and customizing the economy of games, most often I need to create a specific data column so that each cell value is automatically calculated based on a formula with one or several variables (for example, calculating the cost of each weapon based on how much damage it causes and what space takes in player inventory).
As a rule, the variables that are in the formulas are different parameters of one object, each of which occupies its own row in the table, and the values for the other properties of this object are located in the other columns of a specific row.
')
And now I will tell you what methods of working with Excel I use in order to organize the economy of the game, as well as to provide simple changes and updates to the table.
Columns with color markers
In my spreadsheets, I mainly use three categories of columns. I mark the top row of each column with colors, with the help of which it becomes clear to me whether the values in this column need to be edited directly, or whether these values are calculated by the formula. I use the following color markers:
1. Green - values in these columns are edited manually. In addition, such values require more frequent changes.
2. Yellow — in these columns, the values are calculated based on the calculations of the values of a specific area (usually the values from the left column), and changing these values affects a specific range of cells. Formulas in these columns require adjustments, but changes are made to them less often than in the cells of the "green" columns.
3. White - the values in these columns are calculated using formulas that do not require any major changes, since the results of the calculations directly depend on the values of the "yellow" columns, the associated control variables (for example, the minimum-maximum range) or significance factors (can be found in the general table of variables). Thus, the formulas in these columns require changes in the most extreme cases.
Ranges of normalized values
For columns that contain data that may be subject to change, as well as affect the values in other columns, I add new entries to my shared variable table to know the minimum and maximum values in this column. Then I add a new column to store the normalized values for each corresponding cell in the original column.
For example, say, column D is green in the Weapons table, and it contains information about the degree of damage each weapon inflicts. After filling these values manually, I would add a new variable to my variable table called “WeaponDamageMax” and set its value as “= MAX (Weapon [D: D])”, as well as another variable “WeaponDamageMin”, which is calculated by the formula “= MIN (Weapon [D: D]).
Then I would create a new column in the Weapon table, column E, for storing normalized damage values with the formula “= (D1 - WeaponDamageMin) / (WeaponDamageMax - WeaponDamageMin)”.
Distribution formulas
As I said, I use yellow for marking columns in which formulas use normalized input data, and also perform some mathematical operations in order to change the distribution of values within the normalized range. This is done in order to gain greater control over the distribution of values, and is also a way to destroy clusters of values that sometimes appear.
For example, if a developer adds a new, powerful weapon that damages 5,000,000, and the remaining damage values of a weapon in column D are in the range 1-200, one such large value will distort other values with lower rates in the range 0-1, when they will be distributed using WeaponDamageMax in order to get the normalized damage values in column E. In some cases this is desirable, but it happens that you want to split these data clusters and distribute them more evenly across the entire range.
This is where the distribution formula comes in handy. The normalized values can be easily converted to obtain the desired propagation value by applying the distribution curve to existing data. In this case, we will create a new “yellow” column, column F, to store the modified values from column D.
Although this method is very powerful and flexible, it is still easier to visualize and generate curves using third-party tools. These tasks can also be performed in Excel, but personally I prefer to use the following tools to visualize and modify my distribution formulas:
Visualization - graphing calculator :
1. Increase the axes to a range of 0-1
2. Copy the formula from the column where the curves are adjusted, in the field “y1 =”
3. Replace the letter of the column with the normalized values on the "x"
4. Click the "Graph" button to see the transition curve.
Changes - smoothness function generator
1. Change the control points of the curve until it looks the way you want
2. From the “function” field, copy the part after “b + c *” in the last line of the function (the part in brackets)
3. Paste the copied formula into the column where the curve is adjusted.
4. Replace the name “t” with the letter of the column with normalized values (nvcID), the name “ts” with “nvcID ^ 2”, and “tc” with “nvcID ^ 3”.
In the case of normalized damage from weapons in column E, we can try to stretch the cluster of values to the lower part of our range using the initial formula of column F in the form “= (-6.885 * (1 ^ 3) * (1 ^ 2) + 19.885 * (E1 ^ 2) * (E1 ^ 2) + -18,395 * (E1 ^ 3) + 3.825 * (E1 ^ 2) + 2.57 * E1), ".
This formula was originally obtained using the smoothness function generator, which I mentioned above, and can be changed if necessary to adjust the distribution. Again, we apply this formula to the remaining rows in column F, using the typical method of dragging a cell with a formula down the remaining cells in the column, to modify E1 for each row.
Regardless of which distribution formula you use, make sure that the values you get remain within 0-1.
Just ... take what you need and leave some money ...
Range control variables
For the values that will be automatically calculated by the formula (in this example, the price of each weapon), I think it would be useful to be able to control the minimum and maximum values of this parameter in order to keep them within the established range. To do this, I add adjustable minimum / maximum variables that correspond to each “white” column (where calculations are made using the formula), which are filled in with automatically calculated values.
In the case of the price of weapons, I would add two new variables to the general table of variables: WeaponCostMin and WeaponCostMax. You can change them manually at any time — for example, if WeaponCostMax is 90000, but the player can collect a maximum of 50,000 coins, you can easily lower the upper limit of the range by setting the WeaponCostMax value to 50,000, in order to ensure that the game sooner or later but you can buy all the weapons.
Weighted average factors
Values of properties calculated by the formula can be calculated using one or several initial values, which I call “factors”. With their help, you can get the final generated value. For example, when calculating the cost of a weapon, these factors are:
The amount of damage that causes weapons (more damage = higher price)
The amount of space that is required in the inventory (more space = lower price).
At this stage, we believe that each factor has already been normalized and regulated using the distribution function (i.e., column D contains the initial damage values of the weapon, column E contains the normalized damage values from the weapon, and column F contains the final damage values from weapons, adjusted for distribution, which will be used in our value formula).
Let's assume that the final values of damage from weapons are contained in the “yellow” column F, and the final values of the amount of space required in the inventory for a specific weapon are in the “yellow” column U. We want to create a formula for the “white” column C, where the automatically calculated price for each weapon will be stored. One of the simplest formulas for column C, in which our desires and requirements are taken into account, is to find the arithmetic average of these two coefficients by:
“= WeaponCostMin + ((WeaponCostMax - WeaponCostMin) * ((F1 + (1.0 - 1)) / 2.0))”.
Note that in this case, again, you will need to drag the formula down in order to apply it to all the other cells in column C.
So, this will show the cost of each weapon within the specified range, based on the level of damage and the space requirements in the inventory for that weapon. However, it also means that both of these factors equally affect the determination of the final cost, which is not always desirable in the presence of several factors.
In such a situation, a quick, simple, and flexible solution is to convert a formula to a weighted average and add a manually configured value factor for each factor to the general table of variables. This will allow to regulate the importance of each individual factor at its discretion.
In this case, we will add two new variables to the general variable table: WeaponCost_DamageFactorWeight and WeaponCost_SpaceFactorWeight. These values are only relative integer “weights”, so if I want the damage to be more important for calculating the cost of a weapon than the inventory requirements for this weapon, you can set the following values: WeaponCost_DamageFactorWeight - “= 5” and WeaponCost_SpaceFactorWeight - "= 1".
In the end, our formula in column C for calculating the cost of a weapon using a weighted average will look like this:
"= WeaponCostMin + ((WeaponCostMax - WeaponCostMin) * (((F1 * WeaponCost_DamageFactorWeight) + + ((1.0 - U1) * WeaponCost_SpaceFactorWeight))) / (WeaponCost_DamageFactorWeight + WeaponCost_SFactFactFactor
Note that this formula works only if the initial values for damage and inventory space are in the range of 0-1. And this is another reason to normalize the original data before performing various additional operations with them.
findings
The method described above is by no means perfect, but I still talked about it in the hope that at least one of its elements would be useful to someone. And although these methods are very easy, there is always an opportunity to make calculations even simpler, and I would like to see suggestions from other developers on how to simplify the calculation process.
For example, I found that the process of adjusting distribution curves can be a stumbling block for developers who are trying to put such methods into practice without having proper experience with balancing and tuning games. If you, working with curves, find that the calculations are very cumbersome, you can explore alternative methods: for example, the method of linear segment curves. To learn about examples of using linear segment curves for balancing and adjusting the game, read the detailed analysis of time monetization formulas using the example of Clash of Clans written by Wolfgang Gröbner.
I have completed quite a lot of projects on the economics of games using Excel. Today, when configuring and customizing the economy of games, most often I need to create a specific data column so that each cell value is automatically calculated based on a formula with one or several variables (for example, calculating the cost of each weapon based on how much damage it causes and what space takes in player inventory).
As a rule, the variables that are in the formulas are different parameters of one object, each of which occupies its own row in the table, and the values for the other properties of this object are located in the other columns of a specific row.
')
And now I will tell you what methods of working with Excel I use in order to organize the economy of the game, as well as to provide simple changes and updates to the table.
Columns with color markers
In my spreadsheets, I mainly use three categories of columns. I mark the top row of each column with colors, with the help of which it becomes clear to me whether the values in this column need to be edited directly, or whether these values are calculated by the formula. I use the following color markers:
1. Green - values in these columns are edited manually. In addition, such values require more frequent changes.
2. Yellow — in these columns, the values are calculated based on the calculations of the values of a specific area (usually the values from the left column), and changing these values affects a specific range of cells. Formulas in these columns require adjustments, but changes are made to them less often than in the cells of the "green" columns.
3. White - the values in these columns are calculated using formulas that do not require any major changes, since the results of the calculations directly depend on the values of the "yellow" columns, the associated control variables (for example, the minimum-maximum range) or significance factors (can be found in the general table of variables). Thus, the formulas in these columns require changes in the most extreme cases.
Ranges of normalized values
For columns that contain data that may be subject to change, as well as affect the values in other columns, I add new entries to my shared variable table to know the minimum and maximum values in this column. Then I add a new column to store the normalized values for each corresponding cell in the original column.
For example, say, column D is green in the Weapons table, and it contains information about the degree of damage each weapon inflicts. After filling these values manually, I would add a new variable to my variable table called “WeaponDamageMax” and set its value as “= MAX (Weapon [D: D])”, as well as another variable “WeaponDamageMin”, which is calculated by the formula “= MIN (Weapon [D: D]).
Then I would create a new column in the Weapon table, column E, for storing normalized damage values with the formula “= (D1 - WeaponDamageMin) / (WeaponDamageMax - WeaponDamageMin)”.
Distribution formulas
As I said, I use yellow for marking columns in which formulas use normalized input data, and also perform some mathematical operations in order to change the distribution of values within the normalized range. This is done in order to gain greater control over the distribution of values, and is also a way to destroy clusters of values that sometimes appear.
For example, if a developer adds a new, powerful weapon that damages 5,000,000, and the remaining damage values of a weapon in column D are in the range 1-200, one such large value will distort other values with lower rates in the range 0-1, when they will be distributed using WeaponDamageMax in order to get the normalized damage values in column E. In some cases this is desirable, but it happens that you want to split these data clusters and distribute them more evenly across the entire range.
This is where the distribution formula comes in handy. The normalized values can be easily converted to obtain the desired propagation value by applying the distribution curve to existing data. In this case, we will create a new “yellow” column, column F, to store the modified values from column D.
Although this method is very powerful and flexible, it is still easier to visualize and generate curves using third-party tools. These tasks can also be performed in Excel, but personally I prefer to use the following tools to visualize and modify my distribution formulas:
Visualization - graphing calculator :
1. Increase the axes to a range of 0-1
2. Copy the formula from the column where the curves are adjusted, in the field “y1 =”
3. Replace the letter of the column with the normalized values on the "x"
4. Click the "Graph" button to see the transition curve.
Changes - smoothness function generator
1. Change the control points of the curve until it looks the way you want
2. From the “function” field, copy the part after “b + c *” in the last line of the function (the part in brackets)
3. Paste the copied formula into the column where the curve is adjusted.
4. Replace the name “t” with the letter of the column with normalized values (nvcID), the name “ts” with “nvcID ^ 2”, and “tc” with “nvcID ^ 3”.
In the case of normalized damage from weapons in column E, we can try to stretch the cluster of values to the lower part of our range using the initial formula of column F in the form “= (-6.885 * (1 ^ 3) * (1 ^ 2) + 19.885 * (E1 ^ 2) * (E1 ^ 2) + -18,395 * (E1 ^ 3) + 3.825 * (E1 ^ 2) + 2.57 * E1), ".
This formula was originally obtained using the smoothness function generator, which I mentioned above, and can be changed if necessary to adjust the distribution. Again, we apply this formula to the remaining rows in column F, using the typical method of dragging a cell with a formula down the remaining cells in the column, to modify E1 for each row.
Regardless of which distribution formula you use, make sure that the values you get remain within 0-1.
Just ... take what you need and leave some money ...
Range control variables
For the values that will be automatically calculated by the formula (in this example, the price of each weapon), I think it would be useful to be able to control the minimum and maximum values of this parameter in order to keep them within the established range. To do this, I add adjustable minimum / maximum variables that correspond to each “white” column (where calculations are made using the formula), which are filled in with automatically calculated values.
In the case of the price of weapons, I would add two new variables to the general table of variables: WeaponCostMin and WeaponCostMax. You can change them manually at any time — for example, if WeaponCostMax is 90000, but the player can collect a maximum of 50,000 coins, you can easily lower the upper limit of the range by setting the WeaponCostMax value to 50,000, in order to ensure that the game sooner or later but you can buy all the weapons.
Weighted average factors
Values of properties calculated by the formula can be calculated using one or several initial values, which I call “factors”. With their help, you can get the final generated value. For example, when calculating the cost of a weapon, these factors are:
The amount of damage that causes weapons (more damage = higher price)
The amount of space that is required in the inventory (more space = lower price).
At this stage, we believe that each factor has already been normalized and regulated using the distribution function (i.e., column D contains the initial damage values of the weapon, column E contains the normalized damage values from the weapon, and column F contains the final damage values from weapons, adjusted for distribution, which will be used in our value formula).
Let's assume that the final values of damage from weapons are contained in the “yellow” column F, and the final values of the amount of space required in the inventory for a specific weapon are in the “yellow” column U. We want to create a formula for the “white” column C, where the automatically calculated price for each weapon will be stored. One of the simplest formulas for column C, in which our desires and requirements are taken into account, is to find the arithmetic average of these two coefficients by:
“= WeaponCostMin + ((WeaponCostMax - WeaponCostMin) * ((F1 + (1.0 - 1)) / 2.0))”.
Note that in this case, again, you will need to drag the formula down in order to apply it to all the other cells in column C.
So, this will show the cost of each weapon within the specified range, based on the level of damage and the space requirements in the inventory for that weapon. However, it also means that both of these factors equally affect the determination of the final cost, which is not always desirable in the presence of several factors.
In such a situation, a quick, simple, and flexible solution is to convert a formula to a weighted average and add a manually configured value factor for each factor to the general table of variables. This will allow to regulate the importance of each individual factor at its discretion.
In this case, we will add two new variables to the general variable table: WeaponCost_DamageFactorWeight and WeaponCost_SpaceFactorWeight. These values are only relative integer “weights”, so if I want the damage to be more important for calculating the cost of a weapon than the inventory requirements for this weapon, you can set the following values: WeaponCost_DamageFactorWeight - “= 5” and WeaponCost_SpaceFactorWeight - "= 1".
In the end, our formula in column C for calculating the cost of a weapon using a weighted average will look like this:
"= WeaponCostMin + ((WeaponCostMax - WeaponCostMin) * (((F1 * WeaponCost_DamageFactorWeight) + + ((1.0 - U1) * WeaponCost_SpaceFactorWeight))) / (WeaponCost_DamageFactorWeight + WeaponCost_SFactFactFactor
Note that this formula works only if the initial values for damage and inventory space are in the range of 0-1. And this is another reason to normalize the original data before performing various additional operations with them.
findings
The method described above is by no means perfect, but I still talked about it in the hope that at least one of its elements would be useful to someone. And although these methods are very easy, there is always an opportunity to make calculations even simpler, and I would like to see suggestions from other developers on how to simplify the calculation process.
For example, I found that the process of adjusting distribution curves can be a stumbling block for developers who are trying to put such methods into practice without having proper experience with balancing and tuning games. If you, working with curves, find that the calculations are very cumbersome, you can explore alternative methods: for example, the method of linear segment curves. To learn about examples of using linear segment curves for balancing and adjusting the game, read the detailed analysis of time monetization formulas using the example of Clash of Clans written by Wolfgang Gröbner.
Source: https://habr.com/ru/post/259981/
All Articles