BASIC IT
Reference MS EXCEL FUNCTIONS
MS EXCEL FUNCTIONS
2
One of the excellent feature of MS
Excel is its functions, there various
functions in MS Excel to help
different types of data processing.
By the end of the session, we will
understand few of the basic and
important functions which is used
frequently.
MS Excel Functions - Calculations
3
SUM
AVERAGE
SUMIF
COUNT & COUNTA
COUNTIF
POWER
MS Excel Functions -Conditions
4
IF UPPER
AND & OR LOWER
MAX & MIN PROPER
VLOOKUP TRIM
CONCATENATE NOW & TODAY
TODAY & NOW & Function
SUM
5
The first Excel function you should be
familiar with is the one that performs the
basic arithmetic operation of addition
SUM(number1, [number2], …)
Ex:
=SUM(A2:A6) - adds up values in cells A2
through A6.
=SUM(A2, A6) - adds up values in cells A2
and A6.
=SUM(A2:A6)/5 - adds up values in cells A2
through A6, and then divides the sum by 5.
AVERAGE
6
The Excel AVERAGE function does
exactly what its name suggests, i.e.
finds an average, or arithmetic
mean, of numbers. Its syntax is
similar to SUM’s
Examples:
=AVERAGE(A2:A6)
SUMIF
7
The SUMIF function is a worksheet function that
adds all numbers in a range of cells based on one
criteria (for example, is equal to 2000, above
2000).
=SUMIF( range, criteria, [sum_range] ) ** [sum_range] is
optional
Range -The range of cells
that you want to apply
the criteria against.
Criteria- The criteria used
to determine which cells
to add.
sum_range - Optional. It is
the range of cells to sum
together. If this parameter
is omitted, it uses range
as the sum_range.
COUNT & COUNTA
8
If you are curious to While the COUNT
know how many cells in function deals only with
a given range contain those cells that contain
numeric values numbers, the Excel
(numbers or dates), don't COUNTA function counts
waste your time counting all cells that are not
them by hand. The Excel blank, whether they
COUNT function will contain numbers, dates,
bring you the count in a times, text, logical values
heartbeat: of TRUE and FALSE,
errors or empty text
COUNT (value1, [value2], …)
strings (""):
=COUNT(A:A) COUNTA (value1, [value2], …)
=COUNTA(A:A)
COUNTIF
9
If you are looking to find out or count few
cells in a row or column with a criteria,
you can use COUNTIF.
=COUNTIF(range, criteria)
For example, if you want to count no. of
cells which contains below 50 in a range
from A2 to E2.
=COUNTIF(A2:E2,"<50")
POWER
10
Returns the result of the number rasied
to the power.
=POWER(number, power)
=POWER(2,8) 64
IF
11
Judging by the number of IF-related comments
on our blog, it's the most popular function in
Excel. In simple terms, you use an IF formula to
ask Excel to test a certain condition and return
one value or perform one calculation if the
condition is met, and another value or calculation
if the condition is not met:
IF(logical_test, [value_if_true], [value_if_false])
For example, the following IF statement instructs
Excel to check the value in A2 and return "OK" if
it's greater than or equal to 3, "Not OK" if it's less
than 3:
=IF(A2>=3, "OK", "Not OK")
AND & OR
12
These are the two most popular logical
functions to check multiple criteria. The
difference is how they do this:
AND returns TRUE if all of the conditions are met,
FALSE otherwise.
OR returns TRUE if any of the conditions is met,
FALSE otherwise.
While rarely used on their own, these functions come in very
handy as part of bigger formulas
MAX & MIN
13
The MAX and MIN formulas in Excel get
the largest and smallest value in a set of
numbers, respectively. For our sample
data set, the formulas will be as simple
as:
=MAX(A2:A6)
=MIN(A2:A6)
VLOOKUP
14
When you need to find things in a table
or a range by row, you can use VLOOKUP.
For example if you need to find a price of
the object from a list of objects, you can
use VLOOKUP and it will automatically
give you the price of the objects.
=VLOOKUP(Lookup Value, Table Array,
Col Index Number, Range Lookup)
=VLOOKUP(B2, Sheet1!A:B, 2, FALSE)
CONCATENATE
15
In case you want to take values from two
or more cells and combine them into one
cell, use the concatenate operator (&) or
the CONCATENATE function:
CONCATENATE(text1, [text2], …)
= CONCATENATE(A2, B2)
To separate the combined values with a
space, type the space character (" ") in
the arguments list:
=CONCATENATE(A2, " ", B2)
TODAY & NOW
16
To see the current date and time
whenever you open your worksheet
without having to manually update it on
a daily basis, use either
=TODAY() to insert the today's date in a
cell.
=NOW() to insert the current date and time
in a cell.
UPPER, LOWER &
17
PROPER
Upper, Lower & Proper Functions are
used to change case of a cell. Unlike
word, we do not have change case
(Shift+F3) option in excel.
So to change case we use functions, for
example:
=UPPER(A2)
=LOWER(A2)
=PROPER(A2)
TRIM
18
If your obviously correct Excel formulas
return just a bunch of errors, one of the first
things to check is extra spaces in the cells
referenced in your formula (You may be
surprised to know how many leading, trailing
and in-between spaces lurk unnoticed in your
sheets just until something goes wrong!).
There are several ways to remove unwanted
spaces in Excel, with the TRIM function being
the easiest one:
TRIM(text)
=TRIM(A1)
& Function
19
& Function is similar to concatenate.
=A2&B2
If you need space in between the words,
=A2&” “&B2
Reference Link:
TRUE OR FALSE
20
True or False is a simple function used to
check two cells contains the same value.
For example to check if A2 & B2 contains
same name, just type
=A2=B2
If both the cells contain same text, the
answer will be TRUE, if it’s not same, the
answer will be FALSE.
This function can be used to check
spelling errors.
How to delete formula, but
keep calculated value
21
When you remove a formula by pressing the
Delete key, a calculated value is also deleted.
However, you can delete only the formula and
keep the resulting value in the cell. Here's
how:
Select all cells with your formulas.
Press Ctrl + C to copy the selected cells.
Right-click the selection, and then click Paste
Values > Values to paste the calculated
values back to the selected cells. Or, press
the Paste Special shortcut: Shift+F10 and
then V.
Note
22
Enclose text values in double quotes,
but not numbers
Any text included in your Excel formulas
should be enclosed in "quotation marks".
However, you should never do that to
numbers, unless you want Excel to treat
them as text values.
Note
23
Don't format numbers in Excel formulas
Please remember this simple rule: numbers
supplied to your Excel formulas should be
entered without any formatting like decimal
separator or dollar sign. In North America and
some other countries, comma is the default
argument separator, and the dollar sign ($) is
used to make absolute cell references. Using
those characters in numbers may just drive your
Excel crazy So, instead of typing $2,000, simply
type 2000, and then format the output value to
your liking by setting up a custom Excel number
format.
Summary
24
By this session we are able to
understand the use of few functions of
excel for arithmetic calculation like SUM,
AVERAGE, SUMIF, COUNT, COUNTIF.
And also few conditional based functions
like IF, OR, AND, VLOOKUP,
CONCATENATE, NOW, TODAY, TRIM,
MAX, MIN, …
By the end of this session we also learnt
few important notes regarding functions
of excel.
THANK YOU
Any Questions?
