Difference Between Basic Excel and Advance Excel with Formulas and Functions

DATEVALUE

Converts a date in the form of
text to a serial number

DAYS360

Calculates the number of days
between two dates based on a 360-day year

EDATE

Returns the serial number of the
date that is the indicated number of months before or after the start date

EOMONTH

Returns the serial number of the
last day of the month before or after a specified number of months

HOUR

Converts a serial number to an
hour

MINUTE

Converts a serial number to a
minute

MONTH

Converts a serial number to a
month

NETWORKDAYS

Returns the number of whole
workdays between two dates

NOW

Returns the serial number of the current
date and time

SECOND

Converts a serial number to a
second

TIME

Returns the serial number of a
particular time

TIMEVALUE

Converts a time in the form of
text to a serial number

TODAY

Returns the serial number of
today’s date

WEEKDAY

Converts a serial number to a day
of the week

WEEKNUM

Converts a serial number to a
number representing where the week falls numerically with a year

WORKDAY

Returns the serial number of the
date before or after a specified number of workdays

YEAR

Converts a serial number to a year

YEARFRAC

Returns the year fraction
representing the number of whole days between start_date and end_date

DB

Returns the depreciation of an
asset for a specified period by using the fixed-declining balance method

DDB

Returns the depreciation of an
asset for a specified period by using the double-declining balance method or
some other method that you specify

FV

Returns the future value of an
investment

INTRATE

Returns the interest rate for a
fully invested security

IPMT

Returns the interest payment for
an investment for a given period

NPER

Returns the number of periods for
an investment

NPV

Returns the net present value of
an investment based on a series of periodic cash flows and a discount rate

PMT

Returns the periodic payment for
an annuity

PPMT

Returns the payment on the
principal for an investment for a given period

PRICE

Returns the price per $100 face
value of a security that pays periodic interest

PV

Returns the present value of an
investment

RATE

Returns the interest rate per
period of an annuity

Function

Description

AND

Returns TRUE if all of its
arguments are TRUE

FALSE

Returns the logical value FALSE

IF

Specifies a logical test to
perform

IFERROR

Returns a value you specify if a
formula evaluates to an error; otherwise, returns the result of the formula

NOT

Reverses the logic of its argument

OR

Returns TRUE if any argument is TRUE

TRUE

Returns the logical value TRUE

Function

Description

CHOOSE

Chooses a value from a list of
values

COLUMN

Returns the column number of a
reference

COLUMNS

Returns the number of columns in a
reference

HLOOKUP

Looks in the top row of an array
and returns the value of the indicated cell

HYPERLINK

Creates a shortcut or jump that
opens a document stored on a network server, an intranet, or the Internet

INDEX

Uses an index to choose a value
from a reference or array

INDIRECT

Returns a reference indicated by a
text value

LOOKUP

Looks up values in a vector or
array

MATCH

Looks up values in a reference or
array

OFFSET

Returns a reference offset from a
given reference

ROW

Returns the row number of a
reference

ROWS

Returns the number of rows in a
reference

TRANSPOSE

Returns the transpose of an array

VLOOKUP,

Looks in the first column of an
array and moves across the row to return the value of a cell

Function

Description

ABS

Returns the absolute value of a
number

EVEN

Rounds a number up to the nearest
even integer

FACT

Returns the factorial of a number

FACTDOUBLE

Returns the double factorial of a
number

INT

Rounds a number down to the
nearest integer

LCM

Returns the least common multiple

LOG

Returns the logarithm of a number
to a specified base

MOD

Returns the remainder from
division

PI

Returns the value of pi

POWER

Returns the result of a number
raised to a power

PRODUCT

Multiplies its arguments

RAND

Returns a random number between 0
and 1

RANDBETWEEN

Returns a random number between
the numbers you specify

ROMAN

Converts an arabic numeral to
roman, as text

ROUND

Rounds a number to a specified
number of digits

ROUNDDOWN

Rounds a number down, toward zero

ROUNDUP

Rounds a number up, away from zero

SQRT

Returns a positive square root

SUBTOTAL

Returns a subtotal in a list or
database

SUM

Adds its arguments

SUMIF

Adds the cells specified by a
given criteria

SUMIFS

Adds the cells in a range that
meet multiple criteria

SUMPRODUCT

Returns the sum of the products of
corresponding array components

SUMSQ

Returns the sum of the squares of
the arguments

Function

Description

AVERAGE

Returns the average of its
arguments

AVERAGEA

Returns the average of its
arguments, including numbers, text, and logical values

AVERAGEIF

Returns the average (arithmetic
mean) of all the cells in a range that meet a given criteria

AVERAGEIFS

Returns the average (arithmetic
mean) of all cells that meet multiple criteria.

CORREL

Returns the correlation
coefficient between two data sets

COUNT

Counts how many numbers are in the
list of arguments

COUNTA

Counts how many values are in the
list of arguments

COUNTBLANK

Counts the number of blank cells within
a range

COUNTIF

Counts the number of cells within
a range that meet the given criteria

COUNTIFS

Counts the number of cells within
a range that meet multiple criteria

LARGE

Returns the k-th largest value in
a data set

MAX

Returns the maximum value in a
list of arguments

MAXA

Returns the maximum value in a
list of arguments, including numbers, text, and logical values

MEDIAN

Returns the median of the given
numbers

MIN

Returns the minimum value in a
list of arguments

MINA

Returns the smallest value in a
list of arguments, including numbers, text, and logical values

MODE

Returns the most common value in a
data set

RANK

Returns the rank of a number in a
list of numbers

SMALL

Returns the k-th smallest value in
a data set

Function

Description

CHAR

Returns the character specified by
the code number

CLEAN

Removes all nonprintable
characters from text

CODE

Returns a numeric code for the
first character in a text string

CONCATENATE

Joins several text items into one
text item

DOLLAR

Converts a number to text, using
the $ (dollar) currency format

EXACT

Checks to see if two text values
are identical

FIND

Finds one text value within
another (case-sensitive)

FIXED

Formats a number as text with a
fixed number of decimals

LEFT

Returns the leftmost characters
from a text value

LEN

Returns the number of characters
in a text string

LOWER

Converts text to lowercase

MID

Returns a specific number of
characters from a text string starting at the position you specify

PROPER

Capitalizes the first letter in
each word of a text value

REPLACE

Replaces characters within text

REPT

Repeats text a given number of
times

RIGHT

Returns the rightmost characters
from a text value

SEARCH

Finds one text value within
another (not case-sensitive)

SUBSTITUTE

Substitutes new text for old text
in a text string

T

Converts its arguments to text

TEXT

Formats a number and converts it
to text

TRIM

Removes spaces from text

UPPER

Converts text to uppercase

VALUE

Converts a text argument to a
number