Formulas Part 2

Rank

=RANK(value, Range, [is_ascending])

Right and Left

=RIGHT(Beauty and the Beast (2017),4) = 017)
=LEFT(Star Wars: The Last Jedi,3) = Sta

Length and Search

=LEN()
=SEARCH(" ", cell)

Concatenate

=CONCATENATE(value1, value2, value3, ....)

Weekday

=WEEKDAY(date, [type]): evaluates to the day of the week of a date. type is 1, 2 or 3.
type = 1: Sunday is day 1 and Saturday is day 7 (default)
type = 2: Monday is day 1 and Sunday is day 7
type = 3: Monday is day 0 and Sunday is day 6

=DATEDIF(start_date, end_date, unit) "Y": the number of years between two dates
"M": the number of months between two dates
"D": the number of days between two dates

Rounding numbers

ROUND(x, n) rounds x to the nearest n decimal places.
CEILING(x, y) rounds x up to the nearest multiple of y.
FLOOR(x, y) rounds x down to the nearest multiple of y.
=ROUND(0.746, 1) = 0.7
=ROUND(325, -2) = 300

=FLOOR(1.0985) = 1
=FLOOR(1.0985, 0.01) = 1.09

=CEILING(0.7461, 0.1) = 0.8

FLOOR(-1.5) is -2 and CEILING(-1.5) is -1

Google Sheets has two related functions called FLOOR.MATH() and CEILING.MATH(). When given one or two arguments, they behave in the same way as FLOOR() and CEILING() respectively. However, you can pass the value 1 to a third argument to make them round towards or away from zero.

That is, FLOOR.MATH(-1.5, , 1) is -1 and CEILING.MATH(-1.5, , 1) is -2

Random Numbers

=RAND()
=RANDBETWEEN(lo, hi)
=NORMINV(RAND(), 3, 2) mean 3 and standard deviation 2

Logical Operations

=NOT()
=AND()
=OR()

Flow Control

=IF(condition, this op if condition true, else this op)
=IFS(condition1, op1, condition2, op2, .....conditionN, opN)
=SWITCH(Range, "value1", opValue1, "value2", opValue2, ....)