How to construct formulae in Excel

Formulae are created by combining up to six types of object:

• operator
• condition
• value or reference
• range
• informational
• logic helper

Each will be described in detail here. The subsequent formula descriptions will refer back to these, so it’s important that you understand each of them before moving on to the formula descriptions.

Operator

This is the formula itself. It tells Excel what it’s meant to be doing. An opening round bracket ( comes straight after the operator. A closing round bracket ) signifies the end of the formula. The IF in the formula below represents the operator.

=IF(CONDITION,VALUE1,VALUE2)

Condition

This is a test that is deemed either true or false. Excel interprets conditions as having a value of TRUE or FALSE and bases additional logic on which value it is. Below are some examples of conditions.

• A1>B1: If cell A1 is greater than cell B1 then it’s true
• A1=B1: If cells A1 and B1 contain exactly the same value, then it’s true. Note that for text-based comparisons, case doesn't matter. So "LONDON" is considered to be equal to "London" and "LoNdOn"
• A1<>B1: If cell A1 contains a different value to B1, then it’s true
• ISBLANK(A1): If cell A1 is blank, then it’s true
• A1+B1=C1: If the sum of A1 and B1 is equal to C1, then it’s true
• MAX(C:C)>4: If column C contains a value greater than 4, then it’s true

Value

This is either a cell reference, a number or text. Examples include

• A1
• "New York"
• A1+B1
• 45

But because every formula returns a value, the value/reference could itself be a formula—a formula within a formula. So the following formulae can also be considered values

• MAX(A1,B1,C1). This will give the value of the maximum of the three cells
• IF(A1=2,3,4). If cell A1 is equal to 2, this will give the value 3; otherwise it will give the value 4
• VLOOKUP(A1,$C:$E,3,0). This will look up cell A1 in column C and bring back the equivalent value in column E

Range

A range of cells is used in formulae that need to reference groups of cells. A range is always referenced by giving the cell at the top left of the range followed by the cell at the bottom right of the range. These two entries are separated with a colon to signify that a range is being referenced. Ranges always sit within a single sheet.

Examples include:

• A1:A10: the first ten cells of column A
• A1:M1: the first 13 cells of row 1
• A1:M10: the 10 rows and 13 columns bound by columns A and M, and rows 1 and 10

In rare examples, a range can be defined by a list of cells, separated by commas:

• A1,A2,A5,A10.

Informational

An informational argument checks the contents of another cell and returns the value TRUE (1) or FALSE (0) based on what it contains. Examples include:

• ISBLANK(A1): checks whether cell A1 is blank
• ISNA(A1): checks whether cell A1 contains the “#N/A” value (a type of error)
• ISODD(A1): checks whether cell A1 is odd

Logic aides

As their name suggests, logic aides are there to help with logic in conditions. There are three that are regularly used that are worth knowing: AND, OR and NOT. When used, they act like a condition, but it’s worth discussing them specifically.

• AND: this is used to check whether multiple conditions are all true
• Example: AND(A1=A2,B1=B2,C1=C2). If A1=A2, B1=B2 and C1=C2, then this condition will be considered true; otherwise it will be considered false.

• OR: this is used to check whether any one or more of a set of conditions is true
• Example: OR(A1=A2,B1=B2,C1=C2). If A1=A2 or B1=B2 or C1=C2, then this condition will be considered true; otherwise it will be considered false. If more than one of the conditions is true, then it is considered true

• NOT: this is used to reference the opposite of a condition. It’s often useful to combine this with AND or OR functions to get what you’re looking for
• Example: NOT(A1=A2). If A1 is not equal to A2, then this condition will be considered true
• EXAMPLE: NOT(OR(A1=A2,B1=B2,C1=C2)). If any of the conditions are true, then it will be considered false

• Tweet