Excel Formulas
Basic math
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)
|
Simple formatting tricks
Function
|
Formula
|
Example
|
To change a cell to proper case
|
=PROPER(cell)
|
=PROPER(A2)
|
To change a cell to upper
case
|
=UPPER(cell)
|
=UPPER(A2)
|
To change a cell to lower case
|
=LOWER(cell)
|
=LOWER(A2)
|
Conditional statements
Function
|
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)
|
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.
Pulling things apart
Function
|
Formula
|
Example
|
To select a certain number of characters from the left
|
=LEFT(cellwithtext, number of characters to be returned)
| =LEFT(A2, 6) |
To select a certain number of characters from the right
| =RIGHT(cellwithtext, number of characters to be returned) | =RIGHT(A2, 6) |
Find text in a field
|
=SEARCH(“text you want to find”, where you want to find it)
| =SEARCH(“,”, A2) |
Extract information from the middle
| =MID(cellwithtext, start position, number of characters you want returned) | =MID(A2, 9, 4) |
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)
|