# Excel Formula List PDF

Excel Formula List PDF- Are you searching in the internet Excel Formula List? We give you Excel Formula List in this article. Read carefully Excel Formula Article.

Function Formula Example

• To add up the total =SUM(cell range) = SUM(B2:B9)
• To add individual items =Value1 + Value 2 =B2+C2
• Subtract =Value1 - Value 2 =B2-C2
• Multiply =Value1 * Value2 =B2*C2
• Divide =Value1 / Value2 =B2/C2
• Exponents =Value1 ^ Value2 =B2^C2
• Average =AVERAGE(cell range) =AVERAGE(B2:B9)
• Median =MEDIAN(cell range) =MEDIAN(B2:B9)
• Max =MAX(cell range) =MAX(B2:B9)
• Min =MIN(cell range) =MIN(B2:B9)

Absolute cell references

When a formula contains an absolute reference, no matter which cell the formula

occupies the cell reference does not change: if you copy or move the formula, it refers

to the same cell as it did in its original location. In an absolute reference, each part of

the reference (the letter that refers to the row and the number that refers to the column)

is preceded by a “\$” – for example, \$A\$1 is an absolute reference to cell A1. Wherever

the formula is copied or moved, it always refers to cell A1.

Conditional statements

 Funtion Formula Example If statement =IF(logical test, “result if the test answer is true”, “result if the test answer is false”) =IF(B2>69,” Pass”,” Fail”) Exact =EXACT(Value1, value2) =EXACT(B2, C2)

Lookup Within a Range

 Formula Function Example Function Formula Example Looks up a value in the leftmost column and returns a value in the same row of the column you specify. =VLOOKUP(value, table, output column, find closest match?) =VLOOKUP(A2, \$C\$2:\$D\$6, 2,TRUE

0 F

60 D

70 C

80 B

90 A

Pulling things apart

Function

To select a certain number

of characters from the left

Formula

=LEFT(cellwithtext,

number of characters to be

returned)

Example

=LEFT(A2, 6)

To select a certain number

of characters from the right

=RIGHT(cellwithtext,

number of characters to be

returned)

=RIGHT(A2, 6)

Extract information from

the middle

=MID(cellwithtext, start

position, number of

characters you want

returned)

=MID(A2, 9, 4)

Find text in a field =SEARCH(“text you want

to find”, where you want to

find it)

=SEARCH(“,”, A2)

Separate a last name

(Example: Smith, Jane)

LEFT and SEARCH

functions

=LEFT(A2, SEARCH(“,”,

A2)-1)

Separate a first name

(Example: Smith, Jane)

MID and SEARCH

functions

=MID(A2, SEARCH(“,”,

A2)+2, 20)

Putting things together

Function Formula Example

To combine cells with a

space in-between

=CONCATENATE(text, “ ”,

text)

=CONCATENATE(A2, “ “,

B2)

To combine cells with a

space in-between (second

option)

=text & “ “ & text =A2 & “ “ & B2

Dealing with dates

Function Formula Example

Return the year =YEAR(datefield) =YEAR(A2)

Return the month =MONTH(datefield) =MONTH(A2)

Return the day =DAY(datefield) =DAY(A2)

Return the day of the week

(1 = Sunday, 2 = Monday,

3 = Tuesday, etc.)

=WEEKDAY(datefield) =WEEKDAY(A2)

To create a date from year,

month, and day

=DATE(year, month, day) =DATE(B2, C2, D2)