FORMULAS AND FUNCTIONS USED IN EXCEL

Formulas:

 A formula in Excel or Google Sheets is an expression that calculates the value of a cell.
Formulas always start with an equal sign (=). For example, =A1 + B1 adds the values in
cells A1 and B1.

Functions:

 Functions are predefined formulas that perform specific calculations using a range of
cells or arguments. Functions simplify complex calculations. For example, =SUM(A1:A10)
calculates the sum of values from cell A1 to A10.

Mathematical and Trigonometric Functions

 ABS: This function returns the absolute value of a number. It essentially transforms
negative numbers into positive ones while leaving positive numbers unchanged. For
example, using =ABS(-10) would return 10.
 ACOS: The arccosine function returns the angle whose cosine is a specified number. The
input value must be between -1 and 1, as these are the valid ranges for cosine values.
For instance, =ACOS(0.5) would return an angle of 60 degrees (or radians if the output is
set to radians).
 SIN: This function calculates the sine of an angle provided in radians. The sine function is
a fundamental part of trigonometry, which is often used in geometry, physics, and
engineering. For example, =SIN(PI()/6) would return 0.5, which is the sine of 30 degrees.
 COS: Similar to the sine function, COS returns the cosine of an angle measured in
radians. The cosine function represents the adjacent side over the hypotenuse in a right
triangle. For example, =COS(PI()/3) would yield 0.5, as cos(60 degrees) equals 0.5.
 TAN: This function returns the tangent of an angle in radians. The tangent is the ratio of
the sine to the cosine of the angle, making it an important function for many
applications. For instance, =TAN(PI()/4) returns 1, as the tangent of 45 degrees is 1.
 SQRT: The square root function returns the non-negative square root of a number. It is
widely used in statistics, geometry, and algebra. For example, using =SQRT(16) would
yield 4, because 4 is the square root of 16.
Statistical Functions

 AVERAGE: This function calculates the arithmetic mean of a group of numerical values. It
sums all the numbers and divides by the count of those numbers. The AVERAGE function
is widely used to determine overall trends or general performance. For example, using
=AVERAGE(10, 20, 30) would return 20, as the sum of the numbers (10 + 20 + 30) is 60,
and dividing by the count of three gives 20.
 COUNT: This function counts how many entries in a specified list are numbers. It ignores
text, logical values, and blank cells. COUNT is useful for quickly assessing how many valid
data points are in a range. For example, =COUNT(1, 2, 3, "text", TRUE) would return 3, as
only the numeric values (1, 2, and 3) are counted.
 MAX: The MAX function returns the highest value from a list of numbers. It is useful for
identifying the peak value in data sets, such as finding the highest sales figure or the
maximum temperature recorded. For example, =MAX(10, 20, 30, 40) would return 40,
which is the maximum among the provided numbers.
 MIN: In contrast to MAX, the MIN function returns the smallest value from a range of
numbers. This function allows users to quickly identify the lowest point in a dataset, like
the lowest expenditure recorded in a financial report. For example, =MIN(10, 20, 30, 40)
would return 10, as it is the minimum value in the listed numbers.
 MEDIAN: The MEDIAN function calculates the median (the middle value) of a group of
numbers, providing a measure of central tendency that is less affected by outliers than
the average. To compute the median, Excel sorts the numbers and finds the middle
value. If there is an even number of observations, it averages the two middle numbers.
For example, using =MEDIAN(10, 20, 30) would return 20, while =MEDIAN(10, 20, 30,
40) would return 25, the average of 20 and 30.

Logical Functions

 IF: The IF function is perhaps the most versatile logical function in Excel. It evaluates a
condition and returns one value if the condition is TRUE and a different value if it is
FALSE. This function allows users to implement decision-making capabilities in their
spreadsheets. For instance, =IF(A1 > 10, "Over 10", "10 or less") checks whether the
value in cell A1 is greater than 10. If this condition is met, it returns "Over 10";
otherwise, it returns "10 or less". This function is particularly useful for creating dynamic
reports and dashboards.
 AND: This function evaluates multiple conditions and returns TRUE only if all specified
conditions are TRUE. If any of the conditions are FALSE, AND returns FALSE. For example,
=AND(A1 > 0, B1 < 100) checks if the value in cell A1 is greater than 0 and the value in
cell B1 is less than 100 at the same time. If both conditions are met, the function returns
 TRUE; otherwise, it returns FALSE. AND is often used in combination with the IF function
to create more complex logical tests.
 OR: In contrast to AND, the OR function returns TRUE if at least one of the provided
arguments is TRUE. It returns FALSE only if all conditions are FALSE. For instance, =OR(A1
> 10, B1 < 5) checks if either the value in A1 is greater than 10 or the value in B1 is less
than 5. If at least one of these conditions holds true, OR will return TRUE. This function
facilitates flexibility in logical testing, allowing users to set multiple acceptable
conditions.
 NOT: The NOT function is a simple yet powerful tool that inverts the logical value of its
argument. If the argument is TRUE, NOT returns FALSE, and vice versa. For example,
=NOT(A1 > 10) will return FALSE if A1 is greater than 10 and TRUE if A1 is 10 or less. This
function is useful when you need to specifically check for the negation of a condition,
frequently in conjunction with other logical functions.

Text Functions

 CONCATENATE: This function is used to join two or more text strings into a single text
string. Although Excel has since introduced the TEXTJOIN and CONCAT functions as
alternatives, CONCATENATE remains widely used. For example, the formula
=CONCATENATE("Hello, ", "World!") would yield "Hello, World!" by combining the two
strings. Users can also concatenate cell references, such as =CONCATENATE(A1, " ", B1),
to join the text in cell A1 with the text in cell B1, separated by a space.
 LEFT: The LEFT function returns a specified number of characters from the start of a text
string. This function is useful for extracting a portion of a string. For example,
=LEFT("Excel", 2) would return "Ex", as it takes the first two characters from the string
"Excel". This function is particularly helpful when dealing with abbreviations or codes
where only the leading characters are relevant.
 RIGHT: Similar to LEFT, the RIGHT function retrieves a specified number of characters but
from the end of a text string. For instance, =RIGHT("Excel", 2) would return "el",
extracting the last two characters from the string "Excel". This functionality is valuable
for cases where the tail end of a string holds significance, such as extracting file
extensions or identifying version numbers.
 FIND: The FIND function is used to locate a specific substring within another string,
returning the starting position of the substring. It is case-sensitive, meaning that "A" and
"a" would be treated as different characters. For example, =FIND("c", "Excel") would
return 3, since the letter "c" is the third character in the string "Excel". This function is
useful for validating data and extracting specific sections based on known patterns.
 LEN: The LEN function calculates the total number of characters in a text string, including
spaces and punctuation. For example, =LEN("Hello, World!") would return 13,
 accounting for each character in the phrase. This function is particularly helpful for data
validation, such as ensuring that a user input meets character length requirements or for
counting specific elements in strings.

Date and Time Functions

 TODAY: This function returns the current date based on the system's date settings. It is
particularly useful for timestamping documents or for calculations that depend on the
current date, such as age calculation or project deadlines. For example, using =TODAY()
will yield the current date in the format set in Excel.
 NOW: Similar to TODAY, the NOW function returns the current date and time. This
function is utilized when an exact time reference is required, such as in time-sensitive
calculations or logging events. For instance, =NOW() provides both the date and time of
the moment in which the formula was processed.
 DATEDIF: This function calculates the difference between two dates in terms of days,
months, or years. This is essential for tasks like calculating age or project durations. The
syntax is DATEDIF(start_date, end_date, unit), where "unit" can specify whether the
difference should be in days (D), months (M), or years (Y).
 EDATE: This function returns the serial number of the date that is a specified number of
months before or after a given start date. This is useful for due dates that are based on
regular intervals, such as loan maturities. The syntax is EDATE(start_date, months),
where "months" can be positive or negative.

Lookup and Reference Functions

 VLOOKUP: This function searches for a specified value in the first column of a range
(table) and returns a corresponding value from a specified column in the same row. This
is commonly used to retrieve related data across sheets, such as finding a price based on
a product ID. The syntax is VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
 HLOOKUP: Similar to VLOOKUP, HLOOKUP searches for a value in the top row of a table
and returns a value in the same column from a specified row below. This is especially
useful for horizontal data tables. Its syntax is HLOOKUP(lookup_value, table_array,
row_index_num, [range_lookup]).
 INDEX: The INDEX function returns a value or a reference to a value located at a specific
row and column in a given range. This function is often used in combination with MATCH
for more powerful data retrieval. The syntax is INDEX(array, row_num, [column_num]),
where "array" is the range of data, "row_num" specifies the row, and "column_num" is
optional for the column.
  MATCH: This function searches for a specified item in a range and returns the relative
position of that item. It is often used alongside INDEX to locate a value dynamically. The
syntax is MATCH(lookup_value, lookup_array, [match_type]), where "match_type"
specifies the type of match (exact or approximate).

Financial Functions

 PMT: This function calculates the periodic payment for a loan based on constant
payments and a constant interest rate. It is instrumental for loan repayments analysis.
The syntax is PMT(rate, nper, pv, [fv], [type]), where "rate" is the interest rate per period,
"nper" is the total number of payments, and "pv" is the present value of the loan.
 FV: The Future Value function calculates the future value of an investment based on
periodic, constant payments and a fixed interest rate. This is crucial for forecasting the
growth of an investment over time. Its syntax is FV(rate, nper, pmt, [pv], [type]).
 PV: The Present Value function determines the present value of an investment or loan
based on periodic, fixed payments and a constant interest rate. It is essential for
evaluating how much a stream of future cash flows is worth today. The syntax is PV(rate,
nper, pmt, [fv], [type]).

Miscellaneous Functions

 RAND: This function generates a random decimal number between 0 and 1. Being
volatile, each recalculation of the worksheet produces a new random number. It can be
used for simulations or random sampling.
 RANDBETWEEN: In contrast to RAND, RANDBETWEEN returns a random integer
between two specified numbers. The syntax is RANDBETWEEN(bottom, top), where
"bottom" is the smallest value and "top" is the largest value that can be returned. This
function is useful in scenarios requiring random integers, such as game applications or
sampling data.