UNIT 3: FORMULAS AND FUNCTION

INTRODUCTION
This lesson will be about the Formulas and Function of Microsoft
Excel. It offers a range of features such as formulas, charts, pivot tables, and
conditional formatting to help users organize, manipulate, and visualize data
effectively.

LEARNING OBJECTIVES
After the completion of the lecture, students should be able to:
1. identify formulas and functions; and
2. utilize the functions and formulas.

LESSON 11: FORMULAS AND FUNCTION

FORMULA
• used to represent an expression.
• main use of a spreadsheet is to automate calculations.
• spreadsheet recalculates all the values each time a change is made.

Formulas may contain references to other cells, expressions, or functions.

Formulas are equations using numbers and variables to get a result.

CREATING FORMULAS
• Enter formulas in 2 ways:
• by using the Function Wizard
• by typing directly to cell
• Must begin with an = symbol.

1. Formulas
• In Excel, a formula is an expression that operates on values in a range of cells or a
cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell
A1 to cell A3.

2. Functions
• Functions are predefined formulas in Excel. They eliminate laborious manual entry of
formulas while giving them human-friendly names. For example: =SUM(A1:A3). The
function sums all the values from A1 to A3.

Five Time-Saving Ways to Insert Data into Excel

1. Simple insertion: Typing a formula inside the cell
• Typing a formula in a cell or the formula bar is the most straightforward
method of inserting basic Excel formulas. The process usually starts by typing
an equal sign, followed by the name of an Excel function.
• Excel is quite intelligent in that when you start typing the name of the function,
a pop-up function hint will show.
2. Using Insert Function Option from Formulas Tab
• If you want full control of your functions insertion, using the Excel Insert
Function dialogue box is all you ever need. To achieve this, go to the
Formulas tab and select the first menu labeled Insert Function. The dialogue
box will contain all the functions you need to complete your financial analysis.
3. Selecting a Formula from One of the Groups in Formula Tab
• This option is for those who want to delve into their favorite functions quickly.
To find this menu, navigate to the Formulas tab and select your preferred
group. Click to show a sub-menu filled with a list of functions. From there, you
can select your preference. However, if you find your preferred group is not
on the tab, click on the More Functions option – it’s probably just hidden
there.

4. Using AutoSum Option

• For quick and everyday tasks, the AutoSum function is your go-to option. So,
navigate to the Home tab, in the far-right corner, and click the AutoSum
option. Then click the caret to show other hidden formulas. This option is also
available in the Formulas tab first option after the Insert Function option.
 5. Quick Insert: Use Recently Used Tabs
• If you find re-typing your most recent formula a monotonous task, then use
the Recently Used menu. It’s on the Formulas tab, a third menu option just
next to AutoSum.

Seven Basic Excel Formulas For Your Workflow

1. SUM
• The SUM function is the first must-know formula in Excel. It usually
aggregates values from a selection of columns or rows from your selected
range.

=SUM(number1, [number2], …)

Example:

=SUM(B2:G2) – A simple selection that sums the values of a row.

=SUM(A2:A8) – A simple selection that sums the values of a column.

=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums values from

range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from
A12 to A15.

=SUM(A2:A8)/20 – Shows you can also turn your function into a formula.
2. AVERAGE
• The AVERAGE function should remind you of simple averages of data such
as the average number of shareholders in a given shareholding pool.

=AVERAGE(number1, [number2], …)

Example:

=AVERAGE(B2:B11)

Shows a simple average, also similar to (SUM(B2:B11)/10)

3. COUNT
• The COUNT function counts all cells in a given range that contain only
numeric values.

=COUNT(value1, [value2], …)

Example:
• COUNT(A:A) – Counts all values that are numerical in A column.
However, you must adjust the range inside the formula to count rows.
• COUNT(A1:C1) – Now it can count rows.

4. COUNTA
• Like the COUNT function, COUNTA counts all cells in a given rage. However,
it counts all cells regardless of type. That is, unlike COUNT that only counts
numeric, it also counts dates, times, strings, logical values, errors, empty
string, or text.

=COUNTA(value1, [value2], …)
5. IF
• The IF function is often used when you want to sort your data according to a
given logic. The best part of the IF formula is that you can embed formulas
and function in it.

=IF(logical_test, [value_if_true], [value_if_false])

Example:

=IF(C2<D3, ‘TRUE,’ ‘FALSE’) – Checks if the value at C3 is less than the value at
D3. If the logic is true, let the cell value be TRUE, else, FALSE

=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – An example

of a complex IF logic. First, it sums C1 to C10 and D1 to D10, then it
compares the sum. If the sum of C1 to C10 is greater than the sum of D1 to
D10, then it makes the value of a cell equal to the sum of C1 to C10.
Otherwise, it makes it the SUM of C1 to C10.
6. TRIM
• The TRIM function makes sure your functions do not return errors due to
unruly spaces. It ensures that all empty spaces are eliminated. Unlike other
functions that can operate on a range of cells, TRIM only operates on a single
cell. Therefore, it comes with the downside of adding duplicated data in your
spreadsheet.

=TRIM(text)
 7. MAX & MIN
• The MAX and MIN functions help in finding the maximum number and the
minimum number in a range of values.

=MIN(number1, [number2], …)
=MAX(number1, [number2], …)

Example:

=MIN(B2:C11) – Finds the minimum number between column B from B2 and

column C from C2 to row 11 in both columns B and C.

Example:

=MAX(B2:C11) – Similarly, it finds the maximum number between column B from

B2 and column C from C2 to row 11 in both columns B and C.

Starting with anything else causes the formula to be treated as if it were text.
Examples of formulas:
Add cells B4, C4, D4, and E4 : =B4+C4+D4+E4
Subtract cell F10 from cell F9 : =F9-F10
Multiply cells A3 and A4 : =A3*A4
Divide cell D5 by cell D6 : =D5/D6

Note:
ALWAYS use cell references or cell address instead of values in your formulas!
That way, when the values change the formulas gets updated.

Explicit values and cell references

• You can use both explicit values and cell references in a formula

An explicit value is also called a literal value

Formula with only cell references : =b3+b4
Formula with only literal values : =15+46
 Formula w/ both cell ref & literal values : =a1/100

Common ways to use formula and function

TYPES OF OPERATOR

Arithmetic operators
• The addition, subtraction, multiplication and division operators return numerical
results.

Order of Calculation ( OC )
• You can use several operations in one function.
• You can group those operations with parentheses.

Examples:
=3*2+1
=c1*(a1+b1)
=(100*a2-10)+(200*b3-20)+30
=(3+2*(50/b3+3)/7)*(3+b7)

 OC refers to the sequence in which numerical operations are performed.

 Division and multiplication are performed before addition or subtraction.
 There’s a tendency to expect calculations to be made from left to right as the
equation would be read in English.

Calc evaluates entire formula, then based upon programming precedence breaks the
formula down executing multiplication and division operations before other operations.

Therefore, when creating formulas you should test your formula to make sure that the
correct result is being obtained.
Following is an example of the order of calculation in operation

Comparative operators
• returns either a true or false answer.
• If the numbers found in referenced cells are accurately represented, the answer is
TRUE, otherwise FALSE.

Text operators
• common for users to place text in spreadsheets.

Provides:
• What and how this type of data is displayed?
• How text can be joined together in pieces coming from different places on the
spreadsheet?
( & ) – Ampersand concatenation
( “ ) – quotation marks indicates space

Reference operators
• column identifier (letter)
• row identifier (number)
• read from left to right
• a reference refers to a single cell
• it can also refer to a cuboid range or a list of references.

Cell reference
• indicates cell’s location
• provides instructions for how cell data is copied or used in calculations.

Types of Cell references

• Relative cell reference (cell value changes as the formula is copied)
• Absolute cell reference (cell value remains static when copied to other locations)
• Mixed cell reference (combination of an absolute cell and a relative cell)

By default, copying formula that contains cell reference, spreadsheets automatically adjust
cell reference.

You can stop it from automatically adjusting cell reference by using one or more dollar sign
($) in the cell reference.

Example:
D9 $D9
$D$9 D$9
Relative Cell Reference
• based on the relative position of the cell that contains the formula and the cell the
reference refers to.
• D9, A7 and O7

Absolute Cell References

• refers to a cell in a specific location.
$D$9 is an absolute cell reference.

copying a formula with this cell ref, the cell ref will NOT change AT ALL.

Mixed Cell References

• has either an absolute column and relative row, or absolute row and relative column.
• $D9 and D$9

Range operator
• written as a colon ( : )
reference upper left : reference lower right

Common Errors in Formula

• #######
• Cell is too narrow to display the results of the formula.
• To fix, simply make the column wider and the “real” value will be
displayed instead of ###### signs.
• #NAME?
• You used a cell reference in the formula that is not formed correctly.
• Example:
=BB+10 instead of =B3+10
• #VALUE!
• The result of trying to do math with a textual value.
• Example:
=A1*3 where A1 contains the word “hello”
• #DIV/0!
• Trying to divide by zero.
• Example:
=3/A1 where A1 contains 0 (zero)

Circular Reference
• Using a formula that contains a reference to the cell that the formula “lives in”.

Example:
putting the formula =A1+1 in cell A1
putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2
LABORATORY ACTIVITIES

Activity 12: Typing Master Activity & Speed Typing

Activity 13: Inputting Data in Spreadsheet, Inserting Formulas and Functions

Activity 14: Chart & Speed Typing