Welcome to the final part of this three-part series of blog posts introducing Excel formulas. If you haven’t already read part one and part two in this series, then I suggest you read them first. For this final blog post, I will introduce the single Text Operator and some commonly used functions that can be useful from day one.

You can download the example spreadsheets used in this blog post by clicking the button below or by clicking the link **here.**

## Text Operator

The Text Operator in question is the ampersand symbol “**&**.” The ampersand is used to carry out what is referred to as concatenation. Concatenation, more simply put, means to join groups of text (more correctly referred to as a string) together.

One example of using the ampersand operator would be the formula **=C2&B2**, which would join the strings of cells **C2** and **B2** together to create a new string.

When using actual text in a formula, rather than a cell reference, you need to wrap the text in quotation marks (sometimes called speech marks). For example, as shown in the image below, placing the formula **=”John” & “Smith”** in a cell produces the result **John Smith**.

You will notice in this example that there are two ampersand operators. Firstly, joining the string **John**, with a string that is just space ” “, resulting in a new string, “**John** “. The second ampersand then joins the string **Smith** to the result of the first ampersand operation, resulting in the final string, **John Smith**.

## Useful Functions

Excel logically groups built-in functions together. To start with, you will probably find that you only really need to concern yourself with a few functions from the Text, Math & Trig, Date & Time, and Logical functions.

For the remainder of this blog post, I will highlight a few functions from each of the commonly-used groups, to give you some idea of what can be achieved.

### Text Functions

Text functions, as the name would suggest, concern themselves with the manipulation of text strings.

#### SUBSTITUTE Function

The **SUBSTITUTE** function can replace existing text with new text in a string. The syntax of the **SUBSTITUTE** function is, **SUBSTITUTE(text,old_text,new_text,instance_num)**.

Parameter | Description |
---|---|

text | the text parameter is the string that the SUBSTITUTE function will act upon. A cell reference can be used, or, a string of text in quotation marks. This parameter is required. |

old_text | the old_text parameter is the text that will be replaced. This parameter is required. |

new_text | the new_text parameter is the text that will be used to replace the old_text. This parameter is required. |

instance | the instance number parameter defines which occurrence of the old_text that will be replaced. This is useful when you have a long string, and you might want to replace a commonly occurring word. For instance, you might only want to replace the second instance of the word. This parameter is optional, meaning, it isn't always required. However, if it is omitted, all occurrences of the old_text will be replaced. |

An example of how this could be useful would be replacing misspelt, or, incorrectly used words such as ‘there’ and ‘their’.

For more information on the **SUBSTITUTE** function, please click here.

#### PROPER Function

The **PROPER** function is a simple but powerful function and is used to set the first letter of each word, in a string of text to uppercase. All other letters are set to lowercase.

The syntax of the **PROPER** function is **PROPER(text)**.

Parameter | Description |
---|---|

text | the text parameter is the text string that the PROPER function will act upon. A cell reference can be used or a text string in quotation marks. This parameter is required. |

This function can be very useful when managing strings of text that might contain people’s names.

For more information on the **PROPER** function, please click here.

### Math & Trig Functions

Perhaps the most commonly used group of functions in Excel is the Math & Trig group of functions.

#### SUM Function

The **SUM** function was touched upon in part one of this series. However, its importance and usage merit a revisit, not only because of how commonly it is used, but how its basic functionality is used by several other Excel functions as you will see later in this post.

The **SUM** Function adds all values (must be numeric) or referenced cells together. Reference Operators discussed during part two of this series are particularly relevant when using the **SUM** Function.

The syntax of the **SUM** function is **SUM(number1, [number2…])**.

Parameter | Description |
---|---|

number1 | the number1 parameter is the first value or range of cells to be added. This parameter is required. |

number2 | the number2 parameter is a value or range of cells to be added to number1 parameter. This is an optional parameter, you can specify up to 255 of them. |

For more information on the SUM function, please click here.

#### SUMIF Function

The **SUMIF** function builds on the functionality of the **SUM** function. It allows you to specify criteria that can be applied to a range. Any values within the range that meet the criteria will be added together.

The syntax of the **SUMIF** function is **SUMIF(range, criteria, sum_range)**.

Parameter | Description |
---|---|

range | the range parameter is the values that will be tested against the criteria parameter. This parameter is required |

criteria | the criteria parameter is the logical statement that is used to test the values in the range parameter. This parameter is required |

sum_range | the sum_range parameter is the values that will be added together if the criteria parameter is met. This parameter is optional, if it is omitted, then the values in the range parameter that meet the criteria parameter will be added together. |

In the example below, the formula is used to add together all the numbers that are greater than 50. This example doesn’t use the optional parameter.

Another example, using the optional parameter **sum_range** only adds up the cells where the text **Apple** is found in the **range**.

For more information on the **SUMIF** function, please click here.

### Date & Time Functions

A lot of Excel spreadsheets incorporate the need to understand Date and Time information. Therefore, the Date and Time functions can be extremely useful.

#### TODAY Function

The **TODAY** function is very simple, pop it in a cell and it will return today’s date. More precisely, it will return the numeric value that represents today’s date. Excel doesn’t store a date but a number, for more information on this, please click here for an explanation. When a formula uses the **TODAY** function, and the cell containing the formula is formatted as a date, Excel displays this number as a date.

The syntax of the **TODAY** function is **TODAY()**. No parameters needed

In the example below, the **TODAY** function is used twice. Firstly, the cell highlighted in yellow just shows the plain old **TODAY** function showing the date this blog post was written. The second example of the **TODAY** function, highlighted in green, calculates the date in 7 days time by added 7 to the result of the **TODAY** function.

For more information on the **TODAY** function, please click here.

#### EDATE Function

The **EDATE** function, returns the numeric value of the date that is ‘n’ number months after the start date. Remember that Excel stores a date as a numeric value that is then formatted as a date.

The syntax of the **EDATE** function is **EDATE(start_date, months)**.

Parameter | Description |
---|---|

start_date | the start_date parameter is the start date for the function. This parameter is required. |

months | the months parameter is the number of months after the start_date parameter that the function will return the numeric value for. This parameter is required. |

An example of how this function can be useful is on a template spreadsheet used as a 12-month financial forecast. The user would enter a start date in one cell then the spreadsheet would calculate the date of the following 12 months.

For more information on the **EDATE** function, please click here.

### Logical Functions

Logical functions are perhaps the most difficult of the four groups of functions discussed in this blog post to understand. However, Logical functions differ from the other functions because they provide you with a degree of control over the result of the formula. This control over the result depends on the value of the Operands.

#### IF Function

The **IF** function, like the **SUM** function, is one of the most commonly used functions. The **IF** function enables you to implement logic into a spreadsheet based on the value of an Operand. In other words, test the value of x, if the value of x passes the test (returns **TRUE**) the then do y, if the value fails the test (returns **FALSE**) then do z.

The syntax of the **IF** function is **IF(logical_test, value_if_true, [value_if_false])**.

Parameter | Description |
---|---|

logical_test | the logical_test parameter is the logical test that is carried out. |

value_if_true | the value_if_true parameter is what the IF function should do if the logical_test parameter returns TRUE. |

[value_if_false] | the value_if_false parameter is what the IF function should do if the logical_test parameter returns FALSE. The parameter is optional; if the parameter is excluded, the function does nothing if the logical_test parameter returns FALSE. |

In the example below, the cells highlighted in yellow are tested using the **IF** function to see if their value is greater than ten. If the value passes the test (**TRUE**), then the cell adjacent to it, highlighted in green, displays the message “**Is greater than ten**“. If the value fails the test (**FALSE**), then the message “**Is less than or equal to ten**” is displayed.

For more information on the **IF** function, please click here.

#### IFERROR Function

Back in blog post two of this series, you were introduced to some of the errors that you can experience when using Excel formulas. The **IFERROR** function is one of the methods that you can use to manage these errors.

The syntax of the **IFERROR** function is **IFERROR(value, value_if_error)**.

Parameter | Description |
---|---|

value | The value that is displayed if there is no error. This is generally some kind of function that is calculating a value. |

value_if_error | The value that should be used if an error occurs. |

In the example below, the **IFERROR** function is used twice on the same error. Cell **B3** has a formula that divides zero by zero (‘=0/0’) which will return the error **#DIV/0!**. The cell highlighted in green displays the message “**Error Detected**” which demonstrates the functionality. However, this result isn’t much use when calculating numeric values. The cell highlighted in yellow displays zero “**0**“, this could be of more use because it won’t break other formulas that might use the result of this function.

For more information on the **IFERROR** function, and error handling in general, please click here.

While the functions discussed in this blog post certainly do not cover all the functions available in Excel, it should give you an indication of what can be achieved. Future blog posts will cover many of the commonly-used and useful functions in more depth.

I hope you found the final part of this series of blog posts useful.

Please feel free to make comments, ask questions, or provide feedback in the comments box below.

If you liked this post, please share it with your friends on Facebook.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.