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
This entry was posted in Advice and tagged . Bookmark the permalink.

5 Responses to How to construct formulae in Excel

  1. Tony Suckling says:

    Hope you can help... I am trying to work out a formula. Basically I need to minus B1 from A1 to give an answer in c1( easy )( a1 and b1 will be calculated from another sheet so that means that if b1 is greater than a1 I need to calculate the difference from a1 and then minus it off of a1 to give me an answer. so lets say that a1 is 20 and b1 is 10 then c1 would be 10... if a1 was 20 and b1 was 25 then c1 would be 15 as I would take the difference away from a1as a1 is a target amount but if I go over the target amount I will have to pay back anything that is over the target amount e.g 5 in this case.. That is where I am unable to come up with a formula to calculate this.. are you able to help.

  2. Bernard says:

    Can you tell me how to compose the following formula please:
    If A1>A2 then add the value 2 to A3, but if A1=A2 just add the value 1 to A3. Plus whatever the value is in A4 divide by 2 and add that value to A3 also (e.g. if A4 = 1 add 0.5)

  3. Ken Utton says:

    HOW TO MAKE FORMULA WORK
    =B1-B2

  4. Nita Perry says:

    Hope you can help; I need to work out formula for if cell A1 is greater than cell B1 then return value in cell A1 and if cell B1 is greater than A1 then return value of cell B1. Any help would be greatly appreciated.

    Thanks

Comments are closed.