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

Advanced Formulas and Functions

Excel provides an enormous number of established formulas and assistance in auditing and calculating your data. The primary groupings are financial, logical, text, date and time, lookup and reference, math and trigonometry, statistical, engineering, cube, and file-related information.

Financial Formulas

Financial functions are probably one of the most commonly used groups. You can calculate payment plans, interest rates, depreciation, and the yield on securities (just to name a few!). Excel simplifies the process by providing fill-in-the-blanks.

The following example returns a loan payment. The lower chart shows the balance if you pay a different amount.

Enter principle, interest and term in the yellow highlighted cells (C1, C3, C4). The PMT formula should refer to these cells and look like this: =PMT(C3/12,C4,C1). Enter the “actual payment” amount in D2.

Copy the following formulas into the table and drag down to populate the table.

• In cell E7 enter "=C1"
• In cell B8 enter "=$D$2"
• In cell C8 enter "=(E7*$C$3)/12"
• In cell D8 enter "=B8-C8"
• In cdll E8 enter "=E7-D8"

Text Functions

Concatenate

The concatenate function strings together the contents of a series of cells (text1, text2). The order that you select the cells is the order that they are combined into the resulting cell.

Syntax: CONCATENATE(text1,text2,...)

Shortcut: The symbol “&” can also be used instead of the concatenate function (=A2&B2).

Example The following examples combines fields to create FullName and Address fields.

Cell Formula C2 = CONCATENATE(A2," ",B2) note that [text2] is [quote space quote] G2 = CONCATENATE(E2,”, TX”,F2) note that [text2] is [quote comma space TX space quote]

Left, Right

LEFT and RIGHT are useful if you wish to remove extra characters from a cell AND if you are able to specify how many characters to remove from the left or right. The formula requires the cell reference (text) and the number of characters to return (num_chars).

MID performs a similar task of returning reduced characters. This function contains 3 qualifiers: cell reference (text), the position of the character where you wish the text to begin (start_num), and the number of characters to return (num_chars).

Syntax: =LEFT(text,num_chars) or RIGHT(text,num_chars) =MID(text,start_num,num_chars)

Example Cell B2: =LEFT(A2,5) Cell E2: =MID(A2,1,5)

Conditional Functions

Conditional functions, like conditional formatting, are great features to help you highlight or manipulate select information based on specified criteria. Excel evaluates the source against the criteria, and returns a value if the logical test is “true” and a different value for “false”. In the same way, Excel will perform a function, like adding or counting, based on the logical test.

• The elements “value_if_true” and “value_if_false” may be a static value or another formula.
• Up to 7 functions may be nested to create some very elaborate tests.
• If, Countif, and Sumif perform the logical test using single criteria.
• Countifs, and Sumifs perform the logical test on a range of cells that meet multiple criteria.

If

IF is straightforward. The reference cell is tested against criteria and will return a value or perform another function if the test returns true or false. “Logical_test” includes both the cell reference and the criteria, such as “B4 is less than 20.”

Syntax: IF(logical_test,value_if_true,value_if_false)

Example In this example, we are testing against the width of a book. If the width is under .375 in (3/8 in), the book requires a pamphlet binding (pam). If the width were equal or over .375 in, the book would require library binding (LB).

Cell Formula C2 =IF(B2>0.375,"pam","LB")

Countif, Countifs

Countif and Countifs literally count the number of times the test returns “true.” Other “count” functions: count – counts the number of cells that contain numbers, counta – counts the number of cells that are not empty, and countblank – counts the number of empty cells.

Syntax: COUNTIF(range,criteria) COUNTIFS(range1,criteria1,range2,criteria2,...)

Example: In the next example, we are counting how many books require 1) pamphlet binding (pam), 2) Library Binding (LB), and 3) how many books need both Library Binding and Spine Repair (LB/Repair).

Cell H2 =COUNTIF($C$2:$C$5,"pam") Cell H3 =COUNTIFS($D$2:$D$5,"good",$C$2:$C$5,"LB") Cell H4 =COUNTIFS($D$2:$D$5,"poor",$C$2:$C$5,"LB")

Sumif, Sumifs

These functions return a sum of numbers that meet specified criteria. Range is the range of cells to compare against the criteria. A range of cells presented in this way: A2:A100 and an entire row looks like this: A:A. Criteria may be a value or range (“=30” or “=black” or “<2009”). Sum_range is used if the actual items to be added are in a different range than the compared range. If nothing is entered here, the original range is summed.

Syntax: SUMIF(range,criteria,sum_range) SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

note: see that the sum_range comes FIRST in the SUMIFS formula.

Example: Calculate the cost of book repair based on condition of the item.

More Functions

There are many extremely useful functions - following are just a few more examples. Search the Excel Help for “functions” and you’ll find the “List of all functions by category” for a full list of statistical, database, math, financial, and many, many, many more function types.

Len

Syntax: =LEN(text)

• Returns the number of characters in a text string – spaces count as characters. Suggestion: use to determine lengths of each line of address on a label. The US Post office only allows 46 characters per line for mass mailings (as of 2008). Another use is to determine number of characters in a text block for web or print content.

Proper

Syntax: =PROPER(text)

• Capitalizes the first letter of every word (as in “Rebecca Holte”).

Trim

Syntax: =TRIM(text)

• Removes extra spaces from text strings – leaves a single space between words (“Rebecca       Holte” = “Rebecca Holte”).

Rounding

• Adding/multiplying numbers obtained from formula sums, you may see different values than expected, due to the how many decimal points are used and when rounding occurs. You may wish to use a rounding or even/odd function. For “number” you can enter an actual number or cell reference, and “num_digits” indicates how many decimal places you require.

Roundup and Rounddown Syntax: = ROUNDUP(number,num_digits)

Even and Odd Syntax: =EVEN(number)