Basic Formulas

Excel Formulas By Sarib Ali:

What is the use of Average formula?

Average of given numbers

Syntax of Average formula:
average(of this number, [and this number too..])

Examples of Excel Average formula:
average(2,4,6) = 4
average(A1:A5) = average of numbers in A1:A5

What is the use of Int formula?

Converts a decimal number to integer lower than it

Syntax of Int formula:
int(this number)

Examples of Excel Int formula:
int(1.2365) = 1

What is the use of Mod formula?

Tells you what is the reminder after dividing one number with another

Syntax of Mod formula:
mod(of this number, divided by this number)

Examples of Excel Mod formula:
mod(5,3) = 2
mod(3,5) = 3

What is the use of Rand formula?

Gives you a random number to play with

Syntax of Rand formula:
rand()

Examples of Excel Rand formula:
rand() = who knows

What is the use of Round formula?

Rounds a number to nearest decimal you specify

Syntax of Round formula:
round(this number, to this many digits after decimal)

Examples of Excel Round formula:
round(1.2365,0) = 1
round(1.2365,2) = 1.24

What is the use of Sum formula?

Adds a bunch of numbers

Syntax of Sum formula:
sum(add this, [and this ..])

Examples of Excel Sum formula:
sum(1,2,3,4) = 10
sum(5.6,2.3) = 7.9

What is the use of Concatenate formula?

Adds a bunch of text values to one another

Syntax of Concatenate formula:
concatenate(this, [to this..])

Examples of Excel Concatenate formula:
concatenate("one ","big ","text") = one big text
concatenate(A1,A2,A3) = adds the text values in A1,A2 and A3

What is the use of Find formula?

Finds the position of a text in another text

Syntax of Find formula:
find(this, in this text, [start here])

Examples of Excel Find formula:
find("e","hello") = 2
find("m","hello") = ERROR

What is the use of Left formula?

Cuts a text from left

Syntax of Left formula:
left(from this text, this many letters)

Examples of Excel Left formula:
left("Pointy Haired Dilbert rocks", 6) = Pointy
left(A1,5) = first five characters in the cell A1

What is the use of Len formula?

Tells you the length of a given text

Syntax of Len formula:
len(of this text)

Examples of Excel Len formula:
len("hello") = 5
len(A1) = length of the value in cell A1

What is the use of Lower formula?

Converts a text to lower case

Syntax of Lower formula:
lower(this text)

Examples of Excel Lower formula:
lower("Hello") = hello
lower("hEELo") = hello

What is the use of Mid formula?

Gets a portion of text

Syntax of Mid formula:
mid(from this text, start here, this many letters)

Examples of Excel Mid formula:
mid("hello",2,3) = ell
mid("hello",2,99) = ello

What is the use of Proper formula?

Converst text to proper case

Syntax of Proper formula:
proper(this text)

Examples of Excel Proper formula:
proper("hello world") = Hello World
proper("Hello world") = Hello World

What is the use of Rept formula?

Repeats a particular text n number of times

Syntax of Rept formula:
rept(this text, this many number of times)

Examples of Excel Rept formula:
rept("|",5) = |||||
rept("And", 2) = AndAnd

What is the use of Substitute formula?

Substitutes one text with another in the given text

Syntax of Substitute formula:
substitute(in this text, this text, with this text, [at this occurance])

Examples of Excel Substitute formula:
substitute("Pointy Haired Dilbert", "Pointy", "Curly") = Curly Haired Dilbert
substitute("123-123-1234","-","") = 1231231234

What is the use of Trim formula?

Removes un-necessary spaces in a given text

Syntax of Trim formula:
trim(this text)

Examples of Excel Trim formula:
trim(" unusally spaced text ") = unusually spaced text

What is the use of Upper formula?

Gets you upper case text from given one

Syntax of Upper formula:
upper(this text)

Examples of Excel Upper formula:
upper("hello") = HELLO
upper("hELLo") = HELLO

What is the use of Value formula?

Converts text to numbers

Syntax of Value formula:
value(from this text)

Examples of Excel Value formula:
value("1.2365") = 1.2365
value("hello") = ERROR

What is the use of And formula?

Checks whether all conditions are passed or not

Syntax of And formula:
and(list of conditions)

Examples of Excel And formula:
and(true, false) = false
and(true, true) = true

What is the use of Countblank formula?

Counts blank cells in a given list

Syntax of Countblank formula:
countblank(in this list)

Examples of Excel Countblank formula:
countblank(list) = number of blank cells in the list

What is the use of Countif formula?

Counts of items in a list matching a condition

Syntax of Countif formula:
countif(in this range, values meeting this criteria)

Examples of Excel Countif formula:
countif(A1:A20, 1) = counts how many cells have "1" 
countif(A1:A20, "<3") = counts how many cells have less than 3

What is the use of If formula?

Fetches one of the two values based on a condition

Syntax of If formula:
if(is-this-true?, do this, or this)

Examples of Excel If formula:
if(5<10,"hello","world") = hello
if(5>10,"hello","world") = world

What is the use of Large formula?

Finds the nth largest number in a list

Syntax of Large formula:
large(from this list, nth largest number)

Examples of Excel Large formula:
large(list, 2) = 2nd largest number in the list

What is the use of Max formula?

Finds the maximum of a given list of numbers

Syntax of Max formula:
max(of this list of numbers)

Examples of Excel Max formula:
max(1,2,3) = 3
max(A1:A20) = maximum value in the range A1:A20

What is the use of Min formula?

Finds the minimum of a given list of numbers

Syntax of Min formula:
min(of this list of numbers)

Examples of Excel Min formula:
min(1,2,3) = 1
min(A1:A20) = minimum value in the range A1:A20

What is the use of Not formula?

Negates a logical value

Syntax of Not formula:
not(this logical value)

Examples of Excel Not formula:
not(false) = true
not(not(false)) = false

What is the use of Or formula?

Checks whether any condition is met

Syntax of Or formula:
or(list of conditions)

Examples of Excel Or formula:
or(true, false) = true
or(false, false) = false

What is the use of Small formula?

Finds the nth smallest number in a list

Syntax of Small formula:
small(from this list, nth smallest number)

Examples of Excel Small formula:
small(list, 2) = 2nd smallest number in the list

What is the use of Sumif formula?

Sums items in a list matching a condition

Syntax of Sumif formula:
sumif(in this range, values meeting this criteria, [sum-this-range])

Examples of Excel Sumif formula:
sumif(A1:A20, 3) = sums the cells with a value of "3" 
sumif(A1:A20, 3, b1:b20) = same as above but adds values in B1:B20

What is the use of Vlookup formula?

Searches a list for a value you are looking for and returns a corresponding value

Syntax of Vlookup formula:
vlookup(this value, in this list, and get me value in this column, [is-my-list-sorted?])

Examples of Excel Vlookup formula:
vlookup("John", list, 2, false) = finds where Jon is in the list and returns the value in the 2nd column

What is the use of Day formula?

Tells you the day of month from a given date

Syntax of Day formula:
day(of this date)

Examples of Excel Day formula:
day("12/31/1981") = 31
day(today()) = current day

What is the use of Hour formula?

Tells you the hour from a given time

Syntax of Hour formula:
hour(at this time)

Examples of Excel Hour formula:
hour("11:30") = 11
hour(now()) = current hour

What is the use of Minute formula?

Tells you the minutes from a given time

Syntax of Minute formula:
minute(at this time)

Examples of Excel Minute formula:
minute("11:30") = 30
minute(now()) = current minutes

What is the use of Month formula?

Tells you the month from a given date

Syntax of Month formula:
month(of this date)

Examples of Excel Month formula:
month("12/31/1981") = 12
month(today()) = current month

What is the use of Networkdays formula?

Tells you how many working days are there between 2 given dates

Syntax of Networkdays formula:
networkdays(from this date, to this date, [add this holidays as well])

Examples of Excel Networkdays formula:
networkdays("12/1/2008","12/31/2008") = 23
networkdays(TODAY(),TODAY()+30) = total working days in next 30 days

What is the use of Now formula?

Today's date along with current time

Syntax of Now formula:
now()

Examples of Excel Now formula:
now() = today's date along with current time

What is the use of Second formula?

Tells you the seconds from a given time

Syntax of Second formula:
second(at this time)

Examples of Excel Second formula:
second("11:30:45") = 45
second(now()) = current seconds

What is the use of Today formula?

Today's date

Syntax of Today formula:
today()

Examples of Excel Today formula:
today() = today's date

What is the use of Weekday formula?

Tells you the day of week from a given date

Syntax of Weekday formula:
weekday(of this date)

Examples of Excel Weekday formula:
weekday("12/12/1981") = 7
weekday(today()) = current day of week

What is the use of Year formula?

Tells you the year from a given date

Syntax of Year formula:
year(of this date)

Examples of Excel Year formula:
year("12/31/1981") = 1981
year(today()) = current year

What is the use of Fv formula?

Finds out how much a series of payments is worth in future

Syntax of Fv formula:
fv(at this rate, this many payments, of each)

Examples of Excel Fv formula:
fv(10%,12,-1000) = 21,384.28

What is the use of Ipmt formula?

Tells you how much of your mortgage goes towards interest in specified month

Syntax of Ipmt formula:
ipmt(at this rate, on this payment, out of this many payments, for this much amount)

Examples of Excel Ipmt formula:
ipmt(10%,3,12,-100000) = 9017.97

What is the use of Npv formula?

Calculates net present value from a series of future payments

Syntax of Npv formula:
npv(at this rate, list of payments)

Examples of Excel Npv formula:
npv(10%,100,100,100,100,100) = 379.07

What is the use of Pmt formula?

Tells you how much you should pay on your mortage (every month ..)

Syntax of Pmt formula:
pmt(at this rate, this many payments, for this much amount)

Examples of Excel Pmt formula:
pmt(10%,12,-100000) = 14676.33

What is the use of Ppmt formula?

Tells you how much of your mortgage goes towards principle in specified month

Syntax of Ppmt formula:
ppmt(at this rate, on this payment, out of this many payments, for this much amount)

Examples of Excel Ppmt formula:
ppmt(10%,3,12,-100000) = 5658.36

What is the use of Column formula?

Tells you the current column number

Syntax of Column formula:
column([of this cell])

Examples of Excel Column formula:
column() = column number where you wrote this formula
column(C4) = 3

What is the use of Isblank formula?

Checks if the input is blank or not

Syntax of Isblank formula:
isblank(this value)

Examples of Excel Isblank formula:
isblank(A1) = true if A1 is blank
isblank("") = false

What is the use of Iserror formula?

Checks if the input has error or not

Syntax of Iserror formula:
iserror(this value)

Examples of Excel Iserror formula:
iserror(1/0) = true
iserror(0/1) = false

What is the use of Isnumber formula?

Checks if the input is number of not

Syntax of Isnumber formula:
isnumber(this value)

Examples of Excel Isnumber formula:
isnumber(123) = true
isnumber("chandoo") = false

What is the use of Istext formula?

Checks if the input is text or not

Syntax of Istext formula:
istext(this value)

Examples of Excel Istext formula:
istext(123) = false
istext("chandoo") = true

What is the use of Row formula?

Tells you the current row number

Syntax of Row formula:
row([of this cell])

Examples of Excel Row formula:
row() = row number where you wrote this formula
row(C4) = 4