Loading...

Game Development Formulas For Google Sheets

Others

RANDOM NUMBERS
=RAND()
Any random number
 
=RANDBETWEEN(Min,Max)
Example RANDBETWEEN(1-100) would return a value from 1-100, including the 1 & 100

ROUNDING
=ROUND(Value, Decimals)
=ROUNDUP(Value, Decimals)
=ROUNDDOWN(Value, Decimals)

Make a list from a range of data select dropbox cell, Data > Data Validation. select “list from Range” > criteria range (your drop down options) DONE

Conditionals

IF Statements
IF STATEMENTS (true or false)
=IF(logical_expression, value_if_true, value_if_false)
IF Else can be set like:
=IF(logical_expression, value_if_true, IF(logical_expression, value_if_true, IF(logical_expression, value_if_true)))

AND Statements
=AND((LogicA),(LogicB))
If Logic A & B = true, returns true
 
=AND(A2,B2) Checks if A2 AND B2 are TRUE, if so returns TRUE else FALSE

OR Statements
=OR(A2,B2) Checks if A2 OR B2 are TRUE, If so returns TRUE else FALSE
=OR((NOT(A2)),(NOT(B2))) Checks if (A2)InvertAnswer OR (B2)InvertAnswer are TRUE else FALSE

NOT Statements
=NOT(AND(A2,B2)) Checks if (A2 AND B2 are TRUE) then NOT, so invert the answer.

Search

MATCH
MATCH(Search Value, Data Range, Search Type)
Returns the cell reference number, example: 3rd cell down will be 3
*Search Type = 0 for Exact Match

INDEX
INDEX(Data Range, Row From Data Range, Column From Data Range)
Example INDEX(E1:F20, 2, 3) Returns the value within the selected cell.

Using MATCH & INDEX together
MATCH(Search Value, Data Range, Search Type)
INDEX(Data Range, Row From Data Range, Column From Data Range)
 
INDEX(Data Range, MATCH(Search Value, Data Range, Search Type), Column From Data Range)
Will find the requested value, search the designated column for the adjacent value and return it.

Game Specific

Whats the AVERAGE damage an attacking player can do?
=B2+(B2*B3) OR =Attack + (Attack * CriticalDamage)
B2 Attack 10 B3 Critical damage 0.2
result = 12

On AVERAGE what is the amount of damage the attacker will do to the defender?
=(E2 – B7) * (1 – B8) (12-5) * (1-0.1)
=6.3 7 x 0.9
=6.3
=((AVERAGE damage an attacking player can do) – (Mitigation)) * (1 – (DodgeChance eg.0.1))

On AVERAGE what is the number of turns it would take the Attacker to kill the Defender
= Health / (AVERAGE amount of damage the attacker will do to the defender)
Example =100/6.3

Free Fall Calculations

Free Fall of an object
Height=0.5* Gravity *( Time^2)
gravity in m/sec
height= m
time=seconds

Free Fall of an object
Time=SQRT((2*Height)/Gravity)
gravity in m/sec
height= m
time=seconds

Free Fall of an object
Velocity=SQRT((2*Gravity)*Height)
gravity in m/sec
height= m
time=seconds

Free Fall of an object
Height=(velocity^2)/(2*Gravity)
gravity in m/sec
height= m
time=seconds

Magnitudes / Vector Math

Find the Magnitude of a Vector
Vector example: (x,y,z) = (4,7,2)
Magnitude (m/sec)=SQRT  (X^2   +   Y^2   +   Z^2)
=SQRT(4^2 + 7^2 + 2^2)
=8.30662.. m/sec
 
Find the magnitude of 2 vectors (magnitude of VectorB – VectorA)
=SQRT((VectorB.x–VectorA.x)^2 + (VectorB.y-VectorA.y)^2 + (VectorB.z-VectorA.z^2))

Find Vector A + Vector B
Vector example:
Vector A = 4,7,2
VectorB = 7,9,4
A+B   =   (X.a + X.b) , (Y.a + Y.b) , (Z.a + Z.b)
A+B   =   (4+7 ) , (7+9) , (2+4)
A+B   =   11 , 16 , 6
 
Find Vector A – Vector B
A-B   =   (X.a – X.b) , (Y.a – Y.b) , (Z.a – Z.b)
 
find vector from 2 Points R(1,2)   S(3,5)
RtoS = 3-1 , 5-2 =  vector 2,3
StoR = 1-3, 2-5 = vector -2,-3

Find Vector A * Vector B
A*B   =   (X.a * Magnitude of B) , (Y.a * Magnitude of B) , (Z.a * Magnitude of B)
Magnitude(m/sec)= SQRT (X^2 + Y^2 + Z^2)
Vector B * Vector A is simply     X.b * Magnitude of vector A etc..
Example working:
VectorA=4,7,2    Vector B =7,9,4
Magnitude B=SQRT( 7^2 + 9^2 + 4^2)
MagnitudeB=12.08
A*B= (4 * 12.08)  ,  (7*12.08)  ,  (2*12.08)
NewVector=48.33, 84.58, 24.17
 
Find Vector A / Vector B
A/B   =   (X.a / Magnitude of B) , (Y.a / Magnitude of B) , (Z.a / Magnitude of B)
Magnitude(m/sec)= SQRT (X^2 + Y^2 + Z^2)
Vector B / Vector A is simply     X.b / Magnitude of vector A etc..

To Normalize a vector we divide each value by the magnitude of that vector.
Vector A = x,y,z
Norm = X.a / Magnitude of A , Y.a / Magnitude of A , Z.a / Magnitude of A
Magnitude = SQRT(X^2 + Y^2 + Z^2)
 
Working: Vector A = 4,7,2
Magnitude of Vector A = 8.30
=(4/8.30) , (7/8.30) , (2/8.30)
Normalized Vector A = 0.48, 0.84, 0.24

To calculate X = VectorA.x + Normal B.x
From A, Move in the direction of B
=(VectorA.x + NormalB.x)  ,  (VectorA.y + NormalB.y)  ,  (VectorA.z + NormalB.z)
 
Vector A = 4 , 7 , 2
Vector B = 7 , 9 , 4
Normal A  = 0.48 , 0.84 , 0.24
Normal B = 0.58 , 0.74 , 0.33
=(VectorA.x + NormalB.x)  ,  (VectorA.y + NormalB.y)  ,  (VectorA.z + NormalB.z)
=(4 + 0.58) , (7 + 0.74) , (2 + 0.33)
=4.58 , 7.74 , 2.33
 
From B, Move in direction of A
=(VectorB.x + NormalA.x)  ,  (VectorB.y + NormalA.y)  ,  (VectorB.z + NormalA.z)

Find Vertical Angle from PointA to pointB (Requires magnitude)
Radians=ASIN((VectorB.y – VectorA.y) / Magnitude)
convert radians to degrees = RADIANS(degrees)
*Magnitude = magnitude between the 2 points, VectorA=AI, B = Player if calculating AI vision  Cone

Find Horizontal Angle from PointA to pointB
Radians=ATAN((VectorB.z–VectorA.z) / (VectorB.x–VectorA.x))
convert radians to degrees = RADIANS(degrees)
*Magnitude = magnitude between the 2 points, VectorA=AI, B = Player if calculating AI vision  Cone