EXCEL

FUNCTIONS

Wasim Patwari
Empowering aspiring data professionals
through our structured Internship Program,
Mentorship Program, and Job Assistance
guiding your journey from skill-building to
career success.

Get started today:

+91-9607157409
 Save For Later

OBJECTIVES

The aim of this course is to show you how to.

·Work with formulas.
·Use the built−in functions to perform calculations and
manipulate text.

PREREQUISITES

You should have a working knowledge of Microsoft Excel, including

how to enter data, work with cells and ranges, and navigate a
worksheet. You should also be familiar with the Windows desktop
and with general concepts of manipulating windows such as
menus, scrollbars and dialog boxes.

SYMBOL
KEY Note

Tip Activity

Analytics Career Connect Wasim Patwari

 Save For Later

CONTENTS

Introduction to Formulas.................................................................3
1−A: The Components of an Excel Formula ............................................4
1−B: Entering Formulas ..........................................................................................6
1−C: Cell References in Formulas ..................................................................9
Introduction to Functions...............................................................13
2−A: The Components of an Excel Function..........................................14
2−B: Using the AutoSum Feature .................................................................15
2−C: Entering Basic Functions ........................................................................17
2−D: Inserting Functions .....................................................................................21
Working with Formulas..................................................................25
3−A: Relative vs. Absolute Cell Referencing ........................................26
3−B: Copying Formulas.......................................................................................27
3−C: Editing Formulas .........................................................................................29
3−D: Making Sense of Error Messages ....................................................30
More Useful Functions ...................................................................32
A−1: Working with Dates ....................................................................................33
A−2: Working with Text........................................................................................35
A−3: Using the IF Function ...............................................................................38
A−4: Using the PMT Function...........................................................................39
A−5: Count Functions .........................................................................................40

Analytics Career Connect Wasim Patwari

 Save For Later

1
Introduction to Formulas

Excel: Functions
3
 Save For Later

1A: The Components of an Excel Formula

1The distinguishing feature of a spreadsheet program such as Microsoft

Excel is that it allows you to create mathematical formulas and execute
functions. Using formulas and functions will save you time in calculating
data. Excel will automatically update formulas whenever you change the
numbers in your worksheet.

A formula is a set of instructions that you enter in a cell to perform

calculations on values entered into the cells of a worksheet. Formulas
consist of the addresses of the cells containing the values and the
appropriate mathematical operators.

All formulas include three key elements:

1. An equal sign ( = ) to begin the formula.

2.The cell references or values you wish to include in the

calculation.

3.The mathematical operator(s) to be used in the calculation.

Formulas begin with an equal sign ( = ). The equal sign prevents Excel
from interpreting the formula as text, since cell addresses begin with
letters. The formula then includes the values to be calculated with
appropriate mathematical operators placed in between. For example, to
add the values in cells A1 and A2, you would type the formula =A1+A2.

Values in a formula can be:

· A constant value (120)
· A cell (B2)
· A range of cells (B2:B10)
. A worksheet function (SUM)

Mathematical operators that can be used in an Excel formula are

+ Plus sign for addition.

- Minus sign for subtraction.
# Asterisk for multiplication.
/ Front slash for division.
^ Caret symbol for exponents.
() Open and close parentheses to group operations.

Excel: Functions
4
 Save For Later

Order of Operations
Excel calculates a formula from left to right. When more than one
mathematical operator appears in a formula, Excel calculates according
to the standard mathematical order of operations. This order determines
which operations are carried out first.

The table below details the mathematical order of operations.

Parentheses
To change the order of evaluation, enclose in parentheses the part of the
formula to be calculated first. If the outcome of the equation is not
changed by rearranging the parentheses, the law of associativity holds.
For example:

=6+4-8
The solution to this formula is 2.
=(6+4)-8
If parentheses are added or rearranged, the outcome
OR
is still the same.
=6+(4-8)

This is not true for all equations. In some equations the law of associativity
does not hold. That is, the location of parentheses can changes the
outcome of the formulas. For example:

=5+2*3 This formula produces “11” because Excel calculates

multiplication before addition. The formula multiplies
2 by 3 and then adds 5 to the result.
=(5+2)*3 In contrast, if you use parentheses to change the
order of operations, Excel first adds 5 and 2 together
and then multiplies the result by 3 to produce “21”.

Excel: Functions
5
 Save For Later

If you have trouble remembering the ordering of operations the pneumonic

phrase: " please Excuse My Dear Aunt Sally" may help you. The first letter of each
word in the phrase corresponding to on of the operations: Parentheses,
Exponents, Multiplication, Division, Addition, and Subtraction.

1-B: Entering Formulas

Formulas are used to obtain answers based on mathematical equations

that you design. Formulas can be as simple as “=2+2” or as complex as
calculating the depreciation of fixed assets. When creating formulas, you
may use actual values, cell addresses, or a combination of the two.

Examples of Formulas

A formula using constants as values =5+2*3

A formula using cell references as values =A3*(B3−C3)

A formula using cell references and constants as values =A3+B3*2

When you enter a formula in a cell, you can either type the cell addresses
or use the mouse to select the cells and allow Excel to enter the cell
addresses into the formula automatically.
The steps for entering a formula are outlined below:
1. Click the cell in which you want the result of the formula to appear.
2. Type the equal sign [ =].
3. Enter the appropriate constant value sand/or cell references along
with the mathematical symbol(s) for performing your calculation(s).
4. Press[Enter].

Formulas are entered in the cell where you want the result to appear.
Once the formula has been entered:
· The result will be displayed in the current cell
· The formula will be displayed in the Formula bar

Excel: Functions
6
 Save For Later

The Formula Bar

After a formula is entered into the cell, the calculation executes
immediately and the formula itself is visible in the Formula bar. In the
example below, the formula for calculating the subtotal of number of
textbooks is displayed in the formula bar. The formula multiplies the
quantity and price of each textbook and adds the subtotal for each book.

1−1: The Formula Bar

You can also enter a formula in a cell by selecting the cell, and typing the
formula directly into the Formula bar.

You will find that you cannot see the true contents of a cell containing a formula
just by looking at it. what you see in the cell is the result based on the formula
entered In order to view the formula, you must select the cell in which it is stored
and then view the formula contents in the Formula bar.

Excel: Functions
7
 Save For Later

Activity1-1:Entering Formulas Using Constants

In this activity you will practice associativity, using parentheses, and the
order of operations with constants. Create the following formulas either
by typing directly in a cell or into the formula bar. Use the worksheet
entitled “Constants.”

When the Presence and Location of Parentheses Do Not Matter

(Associativity)

1. In cell A1 add together the numbers 12 and 18, then subtract

5 (no parentheses).
2. In cell A2, add together the number 12 and 18 inside
parentheses, then subtract 5.
3. In cell A3, add 12 to 18 minus 5. Surround the 18 minus 5 with
parenthesis.
4. What do you notice about these three formulas:

When the Presence and Location of Parentheses Do Matter

1. In cell C1, multiply 3 and 4, then add 5 (no parentheses).

2. In cell C2, enclose 3 times 4 in parentheses, then add 5.

3. In cell C3, enclose multiply 3 times the sum of 4 and 5

(parentheses around 4 plus 5).

4. What do you notice about these three formulas:

Nested Parentheses

Create formulas in Excel to find the sum of the following equations.

1.((4*10)+8)/3

2.(4*(10+8))/3

3.(4*10)+(8/3)

4.4*((10+8)/3))

Excel: Functions
8
 Save For Later

1-C: Cell References in Formulas

A reference identifies a cell or a range of cells on a worksheet and tells

Excel where to look for the data you want to use in a formula. With
references, you can use data contained in different parts of a worksheet
in one formula or use the value from one cell in several formulas. You can
also refer to cells on the other sheets in the same workbook, and to other
workbooks.

Excel uses the A1 reference style, which refers to columns with letters and
refers to rows with numbers. These letters and numbers are called row
and column headings. To refer to a cell, enter the column letter followed
by the row number. For example, “B2” refers to the cell at the intersection
of column B and row 2.

· Formulas containing constant values will produce a result that will never
change. The formula “=3*4” produces the result “12”.

· A formula containing cell references produces a result that may change

if the data in those cells changes. The formula “=B2+C2” will produce a
result based upon the values in cells B2 and C2.

Activity 1-2: Entering Formulas Using Cell References

You have enrolled in two courses this semester. You want to track your
course expenses in an Excel worksheet. In the worksheet “Courses,” create
a formula in cell C5 that adds the cost of the three Writing Creative
Nonfiction textbooks in cells C2, C3, and C4.

Excel: Functions
9
 Save For Later

Constructing Formulas with the Point Method

Sometimes, not all of the cells you wish to reference in a formula are
visible in the Excel window. In this case, it is more accurate to use the
mouse to select cells while creating a formula. You only need to type the
equal sign ( = ) to start the formula and type each of the other
mathematical operators in the formula.

The key to the point method is to point and click the cells to be included
and to type the operators where appropriate. The following example
provides instructions for a simple addition of two cells “=cell1+cell2”
using the point method.
1. Click the cell in which you want to enter the formula.
2. To start the formula, press [ = ].
3. Point and click on the first cell to be added.
4. Press [ + ].
5. Point and click on the second cell to be added.
6. Press [Enter].

Instead of pressing [Enter] to complete formulas in Excel, you can click the
check mark in the Formula bar [ ].

Activity 1-3: Entering Formulas Using Cell References

Now you will write a formula to subtotal the text book expenses for the
Techniques of Successful Writing course. In the “Courses” worksheet, write a
formula in cell C10 that adds together the textbooks in cells C8 and C9. Use
the pointing method to select the cells.

Excel: Functions
10
 Save For Later

Reference to Another Worksheet

You may want to use the value from a cell in another worksheet within
the same workbook in a formula. In the following example, the Average
worksheet function calculates the average value for the range B1:B10 on
the worksheet named Marketing in the same workbook. Note that the
name of the worksheet and an exclamation point precede the cell
reference.

Reference to Another Workbook

A link is a reference to another workbook. Linking is especially useful when it

is not practical to keep large worksheets together in the same workbook.
Formulas with links to other workbooks are displayed in two ways,
depending on whether the source workbook, the one workbook that
supplies data to a formula, is open or closed.

When the source is open, the link includes the workbook name in square
brackets, followed by the worksheet name, an exclamation point ( ! ), and
the cells that the formula depends on. For example, the following formula
adds the cells C10:C25 from the workbook named Budget.xls.

=SUM([Budget.xls]Annual!C10:C25)

When the source is not open, the link includes the entire path.

=SUM('C:\Reports[Budget.xls]Annual'!C10:C25)

Formulas that link to a defined name (a word or string of characters that

represents a cell, range of cells, formula, or constant value) in another
workbook use the workbook name followed by an exclamation point ( ! ),
and the name. For example, the following formula adds the cells in the
range named Sales from the workbook named Budget.xls.

=SUM(Budget!Sales)

If the name of the other worksheet or work book contains nonalphabetic

characters you must enclose the name ( or the path) with in single
quotation marks.

Excel: Functions
11
 Save For Later

2
Introduction to Functions

Excel: Functions
12
 Save For Later

2-A: The Components of an Excel Function

A variety of functions are provided with Excel to carry out common

calculations on data and manipulate text within a worksheet. These
functions can often take the place of certain types of formulas. Functions
can be a more efficient way of performing mathematical operations.

A function is a built−in Excel formula. You can use functions to simplify

the process of entering formulas. For example, if you wanted to add the
values of cells D1 through D10, you could type the formula
“=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10”. A shorter way would be to use
the SUM function and simply type “=SUM(D1:D10)”.

All functions contain the same syntax:

1. An equal sign ( = ) at the beginning.

2. The Function name follows next.
3. The Arguments (the cell references to be used in the
calculation enclosed in parentheses) come last.

Excel provides over 200 built−in formulas called functions. You can use a
function by itself or in conjunction with other formulas or functions. Some
of the most commonly used functions are described in the table below:

Excel: Functions
13
 Save For Later

2-B: Using the AutoSum Feature

One of the most commonly used functions in Excel is the SUM function,
which calculates the total of the values in a range of cells. Using this
function is easier than typing a formula and each individual cell address.

Since the SUM function is used frequently, there is an AutoSum button on

the Home tab of the Ribbon that enters the formula in the active cell for
you. The AutoSum button is an easy way to sum values in a row or
column of a worksheet.

When you click the AutoSum button, a suggested range for the function
is selected. This suggested range can be changed. If you click the
AutoSum button at the end of a row, the row of values to the left of the
active cell is selected. If you click the AutoSum button at the bottom of a
column, the column of values above the active cell is selected. If there
are values both above and to the left of the active cell, the column of
values above the active cell is selected.

You can also use AutoSum, by first selecting the cells you want to add
together then clicking the AutoSum button. This will put the answer in an
empty cell right following the selected cells.

Excel’s AutoSum feature allows you to quickly create sums, and other
functions, without typing any function syntax.

To calculate a sum using AutoSum:

1. Click on the cell into which you want to calculate a sum.

2. Click the AutoSum button on the Home tab.

3. Excel will highlight a range that it assumes you would like to sum.
· If Excel has chosen the correct range, press [Enter].
· If Excel has chosen the incorrect range, simply click and highlight
the correct range, and then press [Enter].
4. Excel calculates and enters the sum.

Other common functions, such as Average, Min, and Max are also
available from the AutoSum button.

Excel: Functions
14
 Save For Later

To access other common functions using AutoSum:

1. Click on the cell into which you want to calculate a sum.

2. Click the AutoSum drop-down arrow button on the Home tab.

3. Select the function you want to use from the list of commonly used
functions (or select More Functions to search for additional functions).
· If Excel has chosen the correct range, press [Enter].
· If Excel has chosen the incorrect range, simply click and highlight the
correct range, and then press [Enter].
4. 4.Excel calculates and enters the result.

Activity 2-1: AutoSum

One of your New Year’s resolutions was to keep better accounting

records for your personal financial budget. In addition to entering your
course expenses in Excel, you will also be tracking your bill payments.
In this activity, you will total the amount paid for the months of January
through June for each of your Utilities using the AutoSum button.

1. Select the worksheet Bills in the Formulas_Practice file.

2. For each of the utilities listed, find the total spent for the year using
AutoSum.
3. Once you have found the total for each of utilities, using AutoSum find
out how much you spent in total on utilities for the whole year.
4. Save your work.

Excel: Functions
14
 Save For Later

To access other common functions using AutoSum:

1. Click on the cell into which you want to calculate a sum.

2. Click the AutoSum drop-down arrow button on the Home tab.

3. Select the function you want to use from the list of commonly used
functions (or select More Functions to search for additional functions).
· If Excel has chosen the correct range, press [Enter].
· If Excel has chosen the incorrect range, simply click and highlight the
correct range, and then press [Enter].
4. 4.Excel calculates and enters the result.

Activity 2-1: AutoSum

One of your New Year’s resolutions was to keep better accounting

records for your personal financial budget. In addition to entering your
course expenses in Excel, you will also be tracking your bill payments.
In this activity, you will total the amount paid for the months of January
through June for each of your Utilities using the AutoSum button.

1. Select the worksheet Bills in the Formulas_Practice file.

2. For each of the utilities listed, find the total spent for the year using
AutoSum.

3. Once you have found the total for each of utilities, using AutoSum find
out how much you spent in total on utilities for the whole year.

4. Save your work.

Excel: Functions
15
 Save For Later

2-C: Entering Basic Functions

There are multiple ways you can create a function. You can insert
functions manually (by typing them), or you can select from available
functions using the Insert Function dialog box or the Functions List.

The steps for entering a function are outlined below:

1. Click the cell in which you want to display the results of the function.
2. Press [ = ].
3. Enter the Function Name into the cell by one of the methods
described in the table below.
4. Select or enter the Function Arguments (cell references to be used in
the calculation enclosed in parentheses).
5. Press [Enter].

Excel: Functions
16
 Save For Later

Enter Functions Manually

To enter a function manually, you must first click in the cell in which you
want the function to be placed, then type the formula as described in
steps 1 through 6 below for each of the basic functions.
1. Press [ = ].
2. Type the function name [ sum, average, min, max, count ].
3. Press left parenthesis [ ( ].
4. Enter the reference to the cells you wish to use in the calculation.
· Press the colon [ : ] between the first and last cells in a range.
· Press comma [ , ] if you are listing cells.
5. Press right parenthesis [ ) ].
6. Press [ Enter ]. Excel will calculate and enter the result in the cell.

For example, in Figure 2−1 below, Excel calculates the sum of the range of
cells beginning with cell A3 and ending with cell D3. In Figure 2−2 below,
Excel calculates the average of cells H13 and H15.

Activity 2-2: Typing in a Function

You are considering purchasing a new car. In order to make an informed

decision about what car to buy, you would like to calculate the average
amount you spend monthly on gas and car maintenance for the car you
currently own.
1. Find the average amount paid for gas for the months of January
through June by entering the Average function manually. In the
“Bills” worksheet, in cell J13 find the average of cells C11 through H11
typing in the average function.

Instead of typing the cell reference, click and select the range of
cells you wish to calculate. Excel will enter the cell reference for you
in the formula.

2. Find the average spent on auto maintenance.

3. Save your work.

If a cell in the range is empty, it is NOT included in calculating the

average. if a cell in the range contains the number zero, It IS
included in calculating the average.

Excel: Functions
17
 Save For Later

The AutoCalculate Feature

The AutoCalculate feature is helpful when you want to spot−check your

worksheet for accuracy. AutoCalculate performs a simple calculation on
a selected range of cells without making you supply a formula. The
results of the calculation appear on the Status bar along the bottom of
the Excel worksheet window (Figure 2−3). These results are temporary
and are not placed in the worksheet.

To use the AutoCalculate feature:

· Click and highlight the range of cells you want results for.
· By default, average, count, and sum appear in the Status bar.

Excel: Functions
18
 Save For Later

To change the calculations performed by AutoCalculate

1. Right-click on the Status bar to access a shortcut menu.

2. Select/deselect the function(s) from the menu that you would like to
use (Figure 2−4).

Excel: Functions
18
 Save For Later

2-D: Inserting Functions

If you are not sure of the proper syntax of a formula, or you need help
entering a formula, select a formula from the Functions library on the
Formulas tab.

To use a function from the Function Library, click on a category of

functions then select the function you want to use from the list (Figure
2−6).

If you hover over a function in the list, a pop−up window appears

explaining what the function does.

Excel: Functions
19
 Save For Later

Once you choose a function, the Function Arguments dialog box (Figure
2−7) will open, providing you with a text box into which you can insert the
cell range for the formula. Note that the fields in the dialog box will vary
based on the function you select. Click the Collapse button after each
field to select the cells to include in your function. Once you have
selected your cells, click the to return to the dialog box.

Note that certain function, for examples SUM, may not open up
the Functions Arguments dialog box. instead they will try to guess
which cells you want in the formula and select them for you
automatically. You can adjust the selection of needed.

You can also insert a function using the Function button in

the Formula bar. Click the button to bring up a list of all available
functions.

Excel: Functions
20
 Save For Later

Activity 2-3:Using the Function Library

Your best friend has heard you speaking with pride and
excitement about the Excel worksheets you have created to
maintain your personal expenses. He has asked that you help him
create a few formulas in one of his company sales worksheets to
calculate the highest and lowest sales totals for the first and
second quarters.

In this activity, you will calculate the highest sales totals reported
for Quarters 1 and 2 at Benjamin of Brussels Company.

1. In the “Sales” worksheet in cell D18 use the function library to

find the Maximum (MAX) sales for the Q1.
2. In cell E18 find the Maximum sales for Q2.

Excel: Functions
21
 Save For Later

3
Working with Formulas

Excel: Functions
22
 Save For Later

3-A: Relative vs. Absolute Cell Referencing

Understanding relative and absolute cell referencing is essential when

copying and moving formulas. It is important to make sure that they refer
to the correct cells.
Relative Referencing
Calling cells by just their column and row labels (such as A1) is called
relative referencing. When a formula contains relative referencing and it
is copied from one cell to another, Excel does not create an exact copy of
the formula. It will change cell addresses relative to the row and column
they are moved to. If you copy the formula across rows or down columns,
the reference automatically adjusts.

By default, new formulas use relative references. For example, if you copy
a relative reference in cell B2 to cell B3, it automatically adjusts from =A1
to =A2.

To prevent this change, cells must be called by absolute referencing.

Absolute Referencing
An absolute cell reference in a formula, such as $A$1, always refer to a
cell in a specific location. If the position of the cell that contains the
formula changes, the absolute reference remains the same. If you copy
the formula across rows or down columns, the absolute reference does
not adjust.
To create an absolute reference, place dollar signs "$" within the cell
addresses in the formula. For example:

Excel: Functions
23
 Save For Later

By default, new formulas use relative references. If you want them to use
absolute referencing, you will need to change them manually. For
example, if you copy an absolute reference in cell B2 to cell B3, it stays
the same in both cells =$A$1.

3-B: Copying Formulas

As you work in Excel, you will often want to reuse formulas in different
parts of the worksheet. This will save you time because you won’t have to
retype them.

You can quickly and easily copy formulas into adjacent cells by using the
AutoFill Handle (the small black square in the lower−right corner of the
selection.
To copy a formula using AutoFill:
1. Click anywhere inside the cell that you want to copy to select it.
2. Rest the mouse pointer on the Fill Handle at the lower−right corner
of the selected cell.
3. The mouse pointer will change to a solid cross.
4. Once the pointer has changed to a solid cross, click and hold the
mouse button down and drag the Fill Handle to the adjoining cell(s)
that you want to copy into.
5. Release the mouse button. Excel fills the cell(s) with the copied
formula using relative referencing.

Example of AutoFill formula:

Excel: Functions
24
 Save For Later

To copy a formula to a nonadjacent cell, use any of the copy/past

functions in Excel:

Buttons on Home Tab

[ Ctrl ] + [C]
Keyboard [ Ctrl ] + [V]

Activity 3-1: Using AutoFill to Copy a Formula

Your best friend is so happy with the work you did on his Benjamin of
Brussels Excel worksheet. He wonders if it would be a lot of trouble for you
to calculate the remaining highest sales totals for Quarters 3 and 4.

In this activity, you will use AutoFill to quickly calculate the highest sales
totals reported for Quarters 3 and 4 at Benjamin of Brussels Company.

1. Select the worksheet.

2. Select cell E18, that contains the highest sales for Q2. This will be the
formula you copy.
3. Use AutoFill to copy the formula across all the cells up to and
including G18.

Excel: Functions
25
 Save For Later

3-C: Editing Formulas

After entering formulas in your worksheet, you may find that you need to
make some changes to them. When you enter or edit a formula in Excel,
cell references and the borders around the corresponding cells are
color−coded to guide you (Figure 3−7).

To change cell references in a formula:

1. Double-click the cell that contains the formula you want to

change. Excel highlights each cell or range of cells with a different
color.
2. Do one of the following:

· To move a cell or range reference to a different cell or range:

drag the color−coded border of the cell or range to the new cell
or range.
· To include more or fewer cells in a reference: drag a corner of
the border.
· In the formula, select the reference, and type a new one.

3. Press [ Enter ].

Deleting Formulas

To delete a formula:

1. Click the cell that contains the formula.

2. Press [ Delete ].

Excel: Functions
26
 Save For Later

3-D: Making Sense of Error Messages

When you enter an incorrect formula, operand, value, reference, etc.,

Excel displays an error value in the cell. Error values always begin with a
pound sign ( # ). Below is a list of common errors:

#VALUE! The cells used in the formula or function contains numbers

that will not return a logical value amount. Recheck all the
values you used in the formula.

#NAME! Excel doesn’t understand the function name that is entered in

the cell. Make certain that you used the correct name.

#DIV/0 The formula is attempting to divide by 0. Have you referenced

a blank cell or range? Or deleted a value needed in the
formula?

Error Checking Smart Tag

A smart tag appears when you create a formula which Excel believes
may be incorrect. The notification comes to you in the form of a green
triangle in the corner of the cell that may contain the error. When you
select the cell, the Error Checking button appears. Hover your mouse over
the button to display a tool tip that describes the possible problem. Click
the drop−down arrow next to the Error Checking button to display a list of
options available to correct the potential problem.

Excel: Functions
27
 Save For Later

Appendi

x
Excel: Functions
28
 Save For Later

More Useful Functions

A-1: Working with Dates

The DATE Function

Using the DATE function returns the sequential serial number that
represents a particular date. If the cell format was General before the
function was entered, the result is formatted as a date.

Syntax:
DATE(year,month,day)
Year The year argument can be one to four digits. Microsoft Excel
interprets the year argument according to the date system you
are using. By default, Excel for Windows uses the 1900 date
system; Excel for the Mac uses the 1904 date system.

Month is a number representing the month of the year. If month is

greater than 12, month adds that number of months to the first
month in the year specified. For example, DATE(2008,14,2) returns
the serial number representing February 2, 2009.

Day is a number representing the day of the month. If day is greater

than the number of days in the month specified, day adds that
number of days to the first day in the month. For example,
DATE(2008,1,35) returns the serial number representing February
4, 2008.

Excel offers a variety of different ways to include dates and times into
your worksheets. Static and dynamic are two types of dates and times.
Static dates and times will not change but dynamic dates and times will
change as time progresses. Nevertheless, both static and dynamic dates
and times are useful.

Example: Inserting Dynamic Dates

Dynamic dates are updated every time Excel recalculates, which is, by
default, every time [Enter] is pressed. This is useful if you would like the
current date to be displayed every time the worksheet is viewed or
printed.

1. Select the cell in which you want the date to appear.

2. In the cell, type: =today()
3. Press [ Enter ].

Excel: Functions
29
 Save For Later

The current date appears in the cell and will be updated every time Excel
recalculates.

Example: Inserting Static Dates

Static dates are not updated. The date that is inserted into the cell is the
date immediately after the command is entered into the cell. This can be
used to enter the date when the worksheet has been created.

1. Select the cell in which you want the date to appear.

2. Press [ Ctrl ] and [ ; ].

The current date appears in the cell and will not be updated.

Example: Calculate the Number of Days Between Two Dates

You do not have to use the DATE function, or any other function, to
calculate the number of days between two dates. Use the subtraction (−)
operator to do this.

Remember to change the Format for the “Days in between” cell to

the Number format. Select Format > Cells and choose the Number
Category.

Excel: Functions
30
 Save For Later

A-2: Working with Text

UPPER, LOWER and PROPER Functions

You may want to convert text from uppercase to lowercase or from

lowercase to proper case to make it more readable. To change the case
of text, use the UPPER, LOWER, or PROPER functions.

Syntax:

UPPER(text) Changes text to all uppercase.

LOWER(text) Changes text to all lowercase.
PROPER(text) Changes text to title case.

Examples:

TRIM Function

TRIM function returns a text value with the leading and trailing spaces
removed.

Syntax: TRIM(text)
where text is the text value to remove the leading and trailing spaces
from.

Examples:

Excel: Functions
31
 Save For Later

PROPER and TRIM Functions combined:

Functions can be combined to complete multiple tasks at once.

Example:

Paste Special
Often when using functions to clean up data, you want the results
of the function to replace the original range of data. Remember in
the example below, cell B2 actually holds the function:
=Proper(A1) and not the result: Joe Smith. If you were to delete
column A, the function would no longer work.

The solution is to use Paste Special>Values.

In the example above, if you would like to replace column A with

the cleaned version (the results of the function in column B), you
would click on the “B” to select the entire column and select Copy.
Then you would click on the “A” to select the entire A column and
choose Paste Special> Values.

Excel: Functions
32
 Save For Later

CONCATENATE Function

The CONCATENATE function creates a text string by pulling data from

specified fields. This function can join information such as first and last
names, or names and scores, which are in separate fields. Up to 30 fields
may be added together in this fashion.

Syntax:

CONCATENATE (text1,text2,...)
Text1, text2, ... are 1 to 30 text items to be joined into a single text
item. The text items can be text strings, numbers, or single−cell
references.

To add supplementary text between fields, type the text in

quotation marks.
To add a space between fields, type " ".

Example: Concatenate First and Last Names

The “&” operator can be used instead of CONCATENATE to join text

items.

Excel: Functions
33
 Save For Later

A-3: Using the IF Function

The IF Function checks a condition that must be either true or false. If the
condition is true, the function returns one value. If the condition is false,
the function returns another value.

The function has three arguments:

(1) The condition you want to check.

(2) The value to return if the condition is true.
(3) The value to return if the condition is false.

Syntax:

IF(logical_test,value_if_true,value_if_false)
Logical_test is any value or expression that can be evaluated
to TRUE or FALSE. For example, A10=100 is a logical expression; if
the value in cell A10 is equal to 100, the expression evaluates to
TRUE. Otherwise, the expression evaluates to FALSE.

Value_if_true is the value that is returned if logical_test is TRUE.

For example, if this argument is the text string "Within budget"
and the logical_test argument evaluates to TRUE, then the IF
function displays the text "Within budget". If logical_test is TRUE
and value_if_true is blank, this argument returns 0 (zero). To
display the word TRUE, use the logical value TRUE for this
argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is

FALSE. For example, if this argument is the text string "Over
budget" and the logical_test argument evaluates to FALSE, then
the IF function displays the text "Over budget". If logical_test is
FALSE and value_if_false is omitted, (that is, after value_if_true,
there is no comma), then the logical value FALSE is returned. If
logical_test is FALSE and value_if_false is blank (that is, after
value_if_true, there is a comma followed by the closing
parenthesis), then the value 0 (zero) is returned.
Value_if_false can be another formula.

Excel: Functions
34
 Save For Later

Formula Description (Result)

A-4: Using the PMT Function

The PMT Function calculates the payment for a loan based on constant
payments and a constant interest rate.

Syntax:

PMT(rate,nper,pv,fv,type)

Rate is the interest rate per period for the loan. (For example,
use 6%/4 for quarterly payments at 6% APR.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of

future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain

after the last payment is made. If fv is omitted, it is assumed to be
0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments

are due.

Example: Calculating Loan Payments

Excel: Functions
35
 Save For Later

A-5: Count Functions

COUNT and COUNTA

Counts the number of entries in a range of cells.

Syntax:

=COUNT(range)

· COUNT is included as an AutoSum Feature.

· COUNT WILL NOT count blank cells or cells that contain text.
Syntax:

=COUNTA(range)

· Use COUNTA to evaluate cells that contain text.

Grading example:
COUNT is useful if you have a large class and want to determine
how many students have turned in a particular assignment.

COUNTBLANK

Counts the number of blank cells in a particular range.

Syntax:

=COUNTBLANK(range)

COUNTIF

Syntax:

Counts the number of cells that are the same as a particular search
string.
=COUNTIF(range, “string”)
Example:

= COUNTIF(range, “A−”)
=COUNTIF(range, “A”)
=COUNTIF(range, “B+”)

Excel: Functions
36
Kickstart Your Data
Analytics Journey Today!
Internship Program : Get real-world experience through hands-on
projects, preparing you for the professional world.
Mentorship Program : Learn to upskill effectively with expert
guidance and personalized self-learning strategies
Job Assistance Program : Connect with top employers and gain the
tools to confidently secure your dream job.

If you want to become a Data Analyst with the

help of free resources and without investing in
expensive courses, Connect With Us.

FOLLOW US FOR MORE