An Introduction to Excel Formulas (Part 3)

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.

Example Text Concatenation

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
textthe 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_textthe old_text parameter is the text that will be replaced. This parameter is required.
new_textthe new_text parameter is the text that will be used to replace the old_text. This parameter is required.
instancethe 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’.

Example SUBSTITUTE Formula

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
textthe 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.

Example PROPER Formula

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
number1the number1 parameter is the first value or range of cells to be added. This parameter is required.
number2the 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.

Excel SUM() Range Example

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
rangethe range parameter is the values that will be tested against the criteria parameter. This parameter is required
criteriathe criteria parameter is the logical statement that is used to test the values in the range parameter. This parameter is required
sum_rangethe 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.

Example SUMIF Formula

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

Example SUMIF Formula with Optional Parameter

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.

Example Excel 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_datethe start_date parameter is the start date for the function. This parameter is required.
monthsthe 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.

Example Excel EDATE Function

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]).

ParameterDescription
logical_testthe logical_test parameter is the logical test that is carried out.
value_if_truethe 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.

Example Excel IF Function

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
valueThe value that is displayed if there is no error. This is generally some kind of function that is calculating a value.
value_if_errorThe 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.

Example Excel IFERROR 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.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit