Unit-3.

Basic Formulas and Functions

Introduction to formulas and Functions
Formulas and functions are the building blocks of working with numeric data in Excel.
FORMULAS IN EXCEL is an expression that operates on values in a range of cell addresses
and operators. For example, =A1+A2+A3, which finds the sum of the range of values from
cell A1 to cell A3.
Mistakes to avoid when working with formulas in Excel
Remember the rules of Brackets of Division, Multiplication, Addition, &
Subtraction (BODMAS). This means expressions are brackets are evaluated first. For
arithmetic operators, the division is evaluated first followed by multiplication then
addition and subtraction is the last one to be evaluated. Using this rule, we can rewrite
the above formula as =(A2 * D2) / 2. This will ensure that A2 and D2 are first evaluated
then divided by two.
Excel spreadsheet formulas usually work with numeric data; you can take advantage
of data validation to specify the type of data that should be accepted by a cell i.e.
numbers only.
To ensure that you are working with the correct cell addresses referenced in the
formulas, you can press F2 on the keyboard. This will highlight the cell addresses used in
the formula, and you can cross check to ensure they are the desired cell addresses.
When you are working with many rows, you can use serial numbers for all the rows
and have a record count at the bottom of the sheet. You should compare the serial
number count with the record total to ensure that your formulas included all the rows.
What is Function in Excel?
FUNCTION IN EXCEL is a predefined formula that is used for specific values in a particular
order. Function is used for quick tasks like finding the sum, count, average, maximum
value, and minimum values for a range of cells. For example, cell A3 below contains the
SUM function which calculates the sum of the range A1:A2.
SUM for summation of a range of numbers
AVERAGE for calculating the average of a given range of numbers
COUNT for counting the number of items in a given range
The importance of functions
Functions increase user productivity when working with excel. Let’s say you would like to get the
grand total for the above home supplies budget. To make it simpler, you can use a formula to get the
grand total. Using a formula, you would have to reference the cells E4 through to E8 one by one. You
would have to use the following formula.
= E4 + E5 + E6 + E7 + E8
With a function, you would write the above formula as
=SUM (E4:E8)
As you can see from the above function used to get the sum of a range of cells, it is much more
efficient to use a function to get the sum than using the formula which will have to reference a lot of
cells.
An example of a function with one argument that adds a range of cells, A3 through A9:
Basics mathematical and statistical functions
1. The SUM function adds argument values.

Type =SUM(B1:B3) in cell A4.

Press Enter. The sum of cells
B1 to B3, which is 63, appears.

2. Average function
3. Min function

4. Max function
5. Count function

6. The SUMIF Function

Syntax
SUMIF(range,criteria,sum_range)
The SUMIF function can be read as: “Sum or add up sum_range if range meets criteria.”
The AVERAGEIF Function
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
Syntax
AVERAGEIF(range,criteria,average_range)
The AVERAGEIF function can be read as: “Average average_range if range meets
criteria.”
COUNTIF Function
Counts the number of cells within a range that
meet the given criteria.
Syntax:
COUNTIF(range,criteria)
The COUNTIF function can be read as: “Count
frequency or number of times or cells if
range contains criteria.”
MEDIAN: MODE:
Syntax: MEDIAN(number1, [number2], …) Syntax: MODE(number1, [number2], …)
Returns the middle value in a range of Returns the most frequently occurring
numbers when arranged in order. value in a range of numbers.
Example: MEDIAN(A1:A10) returns the median Example: MODE(A1:A10) returns the mode
of values in the range A1 to A10. of values in the range A1 to A10.
STDEVP:
STDEV:
Syntax: STDEVP(number1, [number2], …)
Syntax: STDEV(number1, [number2], …)
Calculates the standard deviation for an
Calculates the standard deviation based on a
entire population.
sample from a population.
Example: STDEVP(A1:A10) calculates the
Example: STDEV(A1:A10) calculates the
standard deviation of values in the range
standard deviation of values in the range A1 to
A1 to A10 for the entire population.
A10.

VAR: VARP:
Syntax: VAR(number1, [number2], …) Syntax: VARP(number1, [number2], …)
Calculates the variance based on a sample Calculates the variance for an entire
from a population. population.
Example: VAR(A1:A10) calculates the variance Example: VARP(A1:A10) calculates the
of values in the range A1 to A10. variance of values in the range A1 to A10 for
the entire population.
CORREL: RAND:
Syntax: CORREL(array1, array2) Syntax: RAND()
Calculates the correlation coefficient between Generates a random number between 0 and
two sets of values. 1.
Example: CORREL(A1:A10, B1:B10) calculates Example: RAND() generates a random
the correlation between the values in ranges number between 0 and 1.
A1 to A10 and B1 to B10.
RANK:
PERCENTILE:
Syntax: RANK(number, range, [order])
Syntax: PERCENTILE(array, k)
, optionally specifying the order (ascending
Calculates the k-th percentile of a dataset.
oReturns the rank of a number within a range
Example: PERCENTILE(A1:A10, 0.5)
of numbersr descending).
calculates the 50th percentile (median) of
Example: RANK(A1, A1:A10, 0) returns the
values in the range A1 to A10.
rank of the value in cell A1 within the range
A1 to A10 in descending order.

Logical functions and conditional statement

Microsoft Excel provides 4 logical functions to work with the logical values. The functions
are AND, OR, XOR and NOT. Excel logical functions return either TRUE or FALSE when their
arguments are evaluated.
The following table provides a short summary of what each logical function does to help
you choose the right formula for a specific task.
Function Description Formula Example Formula Description

Returns TRUE if a value in cell A2 is greater

AND Returns TRUE if all of the =AND(A2>=10, B2<5) than or equal to 10, and a value in B2 is less
arguments evaluate to TRUE. than 5, FALSE otherwise.

Returns TRUE if A2 is greater than or equal to

Returns TRUE if any 10 or B2 is less than 5, or both conditions are
OR argument evaluates to TRUE. =OR(A2>=10, B2<5) met. If neither of the conditions it met, the
formula returns FALSE.

Returns TRUE if either A2 is greater than or

XOR Returns a logical Exclusive Or =XOR(A2>=10, B2<5) equal to 10 or B2 is less than 5. If neither of
of all arguments. the conditions is met or both conditions are
met, the formula returns FALSE.
Returns the reversed logical
value of its argument. I.e. If
NOT the argument is FALSE, then =NOT(A2>=10) Returns FALSE if a value in cell A1 is greater
TRUE is returned and vice than or equal to 10; TRUE otherwise.
versa.

Microsoft Excel provides 3 "conditional" functions - IF, IFERROR and IFNA.

The syntax for the Excel AND

function is as follows:
AND(logical1, [logical2], …)
One of the most common uses of the
Excel AND function is found in the
logical test argument of the IF
function to test several conditions
instead of just one.
=IF(AND(A2="Bananas", B2>C2),
"Good", "Bad")

OR function
The syntax of the Excel OR
function is very similar to
AND:
OR(logical1, [logical2], …)
IF function with nested OR
=IF(OR(B2>30, C2>20), "Good", "Bad")
The formula returns "Good" if a number in cell B3 is greater than 30 or the
number in C2 is greater than 20, "Bad" otherwise.
XOR function
The syntax of the XOR function is identical to OR's :
XOR(logical1, [logical2],…)
When more logical statements are added, the XOR function in Excel results in:
TRUE if an odd number of the arguments evaluate to TRUE;
FALSE if is the total number of TRUE statements is even, or if all statements are FALSE.
NOT function
The NOT function is one of the simplest Excel functions in terms of syntax:
NOT(logical)

IF Function
The IF function is a premade function in Excel, which returns values based on
a true or false condition.
It is typed =IF and has 3 parts:
=IF(logical_test, [value_if_true], [value_if_false])
IFS Function
The IFS function is a premade function in Excel, which returns values based on one or
more true or false conditions.
It is typed =IFS and has two or more parts:
=IFS(logical_test1, value_if_true1, [logical_test2,value_if_true2], [logical_test3
; ...)

IF statement with multiple conditions

Excel IF function with multiple criteria
 Multiple AND conditions
 Multiple OR conditions
 AND & OR statements in one formula
Multiple nested IF statements
IF statement with multiple conditions (AND logic)
Syntax
IF(AND(condition1, condition2, …), value_if_true, value_if_false)
Example
=IF(AND(B2>50, C2>50), "Pass", "Fail")

IF function with multiple conditions (OR logic)

Syntax
IF(OR(condition1, condition2, …),
value_if_true, value_if_false)
Example
=IF(OR(B2>50, C2>50), "Pass", "Fail")
IF with multiple AND & OR statements
Example
=IF(OR(AND(B2>50, C2>50), AND(B2>40,
C2>60), "Pass", "Fail")

Nested IF statement to check multiple logical tests

IF with ISNUMBER, ISTEXT and ISBLANK

To identify text, numbers and blank cells, Microsoft Excel provides special functions such as
ISTEXT, ISNUMBER and ISBLANK. By placing them in the logical tests of three nested IF
statements, you can identify all different data types in one go:
=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", "")))
IF ISERROR / ISNA formula in Excel
The modern versions of Excel have special functions to trap errors and replace them with
another calculation or predefined value - IFERROR (in Excel 2007 and later) and IFNA (in
Excel 2013 and later). In earlier Excel versions, you can use the IF ISERROR and IF ISNA
combinations instead.
The difference is that IFERROR and ISERROR handle all possible Excel errors, including
#VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While IFNA and ISNA
specialize solely in #N/A errors.
For example, to replace the "divide by zero" error (#DIV/0!) with your custom text, you can
use the following formula:
=IF(ISERROR(A2/B2), "N/A", A2/B2)
Error handling and troubleshooting
Error handling in Excel involves managing and addressing errors that may occur in formulas or
functions. This includes using functions like IFERROR, ISERROR, or IF statements to check for errors and
provide alternative results or messages when errors occur in a cell. It helps prevent unexpected errors
from disrupting calculations and improves the overall reliability of your Excel sheets.
Excel Troubleshooting is the process of rectifying the errors from a formula and nullifying them. We
will see some methods to troubleshoot the formula errors in Excel.
Handling errors in Excel formulas can be a bit tricky, but here are some tips to deal with them
smoothly:
1. IFERROR Function: This is like a superhero in Excel for handling errors. You can use it to display a
custom message or value when a formula encounters an error. For example, `=IFERROR(formula,
"Oops! Something went wrong")`.
2. Error Checking: Excel has a built-in error checking feature (the little green triangle in the top-left
corner of a cell). Click on it to see options like "Show Calculation Steps" or "Ignore Error". It helps you
understand why the error occurred.
3. ISERROR Function: This function checks if a cell contains an error. You can use it in combination
with other functions to take different actions based on whether a formula produces an error or not.
4. Avoiding Divide by Zero Errors: If you have formulas that divide by a cell value, make sure to
include logic to handle cases where the divisor is zero. You can use `IF` or `IFERROR` to check for zero
before performing the division.
5. Error Types: Different errors in Excel formulas like #DIV/0!, #VALUE!, #REF!, etc., indicate different
issues. Understanding these can help you debug formulas faster.
6. Formula Auditing: Use tools like "Trace Error" or "Evaluate Formula" to step through your formulas
and see where errors are occurring. It helps pinpoint the exact problem area.
7. Practice and Patience: Dealing with errors in Excel formulas gets easier with practice. Don’t worry
if you encounter errors; it’s a natural part of working with formulas.
How can one display possible errors and solutions in Excel?
You can display possible errors in Excel using the built-in 'Error Checking' tool found under
the 'Formulas' tab. This tool identifies common errors such as DIV/0!, #N/A, or #VALUE!
and offers possible solutions. Additionally, you can utilise the 'IFERROR' function to handle
errors and display custom messages.
What are the five types of Excel errors?
The five types of Excel errors are: #DIV/0!, #N/A, #NAME?, #NULL!, and #NUM!. These
signify different types of computation or data input errors within your Excel sheet.
How can one remove all errors in Excel?
Select the range where you want to remove errors. Click 'Home' > 'Find & Select' > 'Go To
Special'. Select 'Formulas', uncheck all boxes except 'Errors', click 'OK'. Press 'Delete'.
Advanced Excel Formulas and Functions
These functions can help you perform complex calculations, analyze data, and much more.
Excel has over 450 functions, and I’m sure you’ve heard of popular ones like VLOOKUP,
SUMIF, and COUNTIF. You can use these advanced functions alone or combine different
functions to create more advanced Excel functions.
VLOOKUP() Function
Here V stands for Vertical. In simple words, it is a function that helps us to search certain
data in a column(also called a table array) and then it gives values from different columns
but from the same row.
Formula: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
XLOOKUP() Function
The XLOOKUP function in Excel searches a range or array for a specified value and returns
the associated value from another column. It can search both vertically and horizontally
and perform exact (default), fuzzy (closest), or wildcard (partial) matching.
Formula: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])

if_not_found, match_mode and Search_mode are Optional.

MATCH() Function
The MATCH() function in Excel allows you to find the position of a specified value within a range. It is
commonly used in combination with other functions like INDEX() to perform advanced lookup and
retrieval operations.
Formula: MATCH(lookup_value, lookup_array, [match_type])

INDEX+ MATCH Function

The combination of INDEX and MATCH functions in Excel is a powerful way to look up and retrieve
specific data from a table.
The INDEX function returns the value of a cell in a specified row and column within a range. While the
MATCH function is used to find the position of a lookup value within a given range.
When used together, they allow you to perform a two-dimensional lookup, where you can find a value
based on both row and column criteria.
Formula(INDEXFunction):INDEX(array,row_num,[column_num])
Formula (MATCH Function): MATCH(lookup_value, lookup_array, [match_type])
What is the VLOOKUP + CHOOSE Function?
The combination of VLOOKUP and CHOOSE functions in Excel allows you to perform more
complex lookups and get data from different columns based on a single criterion.
The VLOOKUP function looks for a value in the leftmost column of a range and returns a
corresponding value from a specified column within that range.
The CHOOSE function allows you to select a value from a list of values based on a numeric
index. When used together, you can dynamically choose which column to look up the data
based on the result of another function.
Formula (VLOOKUP): VLOOKUP(lookup_value, table_array, col_index_num, range_lookup])
Formula ( CHOOSE): CHOOSE(index_num, value1, [value2], …)
CONCATENATE() Function
The CONCATENATE function in Excel is used to combine multiple text strings into a single
string. It allows you to join together the contents of two or more cells or add additional text
or characters in between the cell values
Formula: CONCATENATE(text1, [text2], …)

REPT() Function
The REPT() function in Excel allows you to repeat a text string a specified number of times.
It is useful for creating repeated patterns or generating repetitive text values.
Formula: =REPT(text, number_times)
TYPE() Function
The TYPE() function in Excel allows you to determine the type of data contained within a
cell. It returns a numerical code that corresponds to a specific data type. This function is
useful when you need to identify the type of data stored in a cell or a formula result.
Formula: =TYPE(value)
The TYPE function returns the following
numerical codes for different data types:
1: Number
2: Text
4: Logical (Boolean)
16: Error
64: Array
128: Cell reference
LEFT_MID_RIGHT Function
The LEFT, MID, and RIGHT functions in Excel are used to extract parts of a text string. The
LEFT function extracts the first n characters from a text string, the MID function extracts a
specified number of characters from a text string starting at a specified position, and the
RIGHT function extracts the last n characters from a text string.

CONVERT() Function
The CONVERT() function in Excel is a powerful tool for unit conversion. It allows you to
convert a value from one measurement unit to another.
Formula: =CONVERT(number, from_base, to_base)
SUMPRODUCT() Function
The SUMPRODUCT() function in Excel is a versatile tool for performing calculations on
arrays or ranges. It allows you to multiply corresponding values in multiple arrays or ranges
and then sum the products.
Formula: =SUMPRODUCT(array1, array2, …)
IFERROR() Function
The IFERROR() function in Excel allows you to handle and manage errors in formulas. It
helps you control the output or display a specific value when an error occurs. In simple
words 1
The IFERROR function in Excel returns a value you specify if a formula evaluates to an
error; otherwise, it returns the result of the formula
Formula: =IFERROR(formula, value_if_error)