Excel Formulas

The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use it. In this post I want to expose some lesser known quirks and ways it can be used.  If the reference/array is a single row, you can put the col_num in the row_num argument’s position …

The Excel XOR function, or ‘exclusive OR’ as it’s known in the programming world, performs a test on an array, range or one or more expressions. It was introduced in Excel 2013. Syntax: =XOR( logical1, [logical2],...) logical1 An expression (a formula that results in a single value), array or reference that evaluates to TRUE or …

Creating a reducing Data Validation list is easy with the new dynamic array formulas. Let’s say we have a list of jobs currently in progress (column B in the image below). Each contractor can work on no more than two jobs and we have four contractors available (column E). As we assign contractors to jobs …

Typically if you need to lookup a value from a table you'd employ VLOOKUP or INDEX/MATCH. However if your data looks like this and you are looking up by Employee name then you'll run into some issues because there are two employees with the name John. Both the VLOOKUP and INDEX/MATCH approach will have problems …

Excel Goal Seek is another tool in the What-if Analysis suite on the Data tab of the ribbon. Goal Seek makes it easy to find the value required to meet a target value you specify. Watch the Excel Goal Seek Video Download Workbook Enter your email address below to download the sample workbook. Get Workbook …

Dates and times are stored in Excel as decimals known as serial numbers. The date is represented by the whole number part of the serial number and the time by the decimal part. In Excel dates start from 1 Jan 1900, which is represented by the number 1. When you see a date or a …

Excel Data Tables are one of the What-if Analysis tools that we have available to aid our decision making. They don't require knowledge of any new fancy formulas and are super quick to build. With Excel Data Tables we can perform what-if analysis with one or two variables, which makes it quick and easy to …

The Excel CONCAT function, new in Excel 2019, replaces the legacy CONCATENATE function*. It’s a simple function that joins the values from two or more cells together into a single text string. Syntax: =CONCAT(text1, [text2], …) Where text1 is the contents of a cell or cells containing text, numbers, references to other cells, formulas, or …

If we want to find whether or not a character or string occurs within another string we can use FIND or SEARCH. But these functions only tell us if a string (or character) exist in another string, they don't tell us if the string we are looking for occurs multiple times. For example, we have …

There are many Excel Calendar templates available and if you look at them closely, you’ll see they have some mind-boggling date formulas. Adam, one of our members, sent me in a calendar he has been using for 10+ years and asked if I could explain how some of the formulas worked. The original source of …

One of our most popular blog posts is Excel IF AND OR Functions Explained which has over 800 comments at time of writing. The vast majority of questions are along the lines of I want to multiply a value by a percentage. In some cases, we need to enter the word "Special" instead of the …

Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA is not the best answer, using a formula is. What this tells me is that people don't know how to use Excel's functions and when they come up against a problem they think is difficult, …

The SUBSTITUTE function allows you to change text in a string, or a range of strings, but there is more to it than that. By combining it with other functions you can use it to do things like count how many times a section of text appears in a cell or range, or find the …

It’s often difficult enough to figure out the correct Excel functions to use, but it’s significantly harder if you also need to translate the function into the language of your localized version of Excel. Introducing the Excel Functions Translator. It’s a free add-in available for Excel Online, Excel 2013, Excel 2016 for Desktop, Office 365, …

The Excel ADDRESS Function returns a cell address for specified row and column coordinates.  Optional arguments allow you to specify the style of address (A1 or R1C1), the sheet name it refers to and whether the reference is absolute or relative. Excel ADDRESS Function Syntax Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Note: Arguments in square …

We often need to extract the date or time portion from a date-time serial number for use in other functions. We can use the INT function to extract the date portion, and the MOD function to extract the time portion. See examples below. The INT function returns the integer or whole number portion of the …

The Excel MOD Function is probably one of the most confusing to understand, unless you’re a math whizz, which I am not. After all this is why I use Excel. It’s my giant calculator of choice. Anyhow, just because I find MOD confusing, doesn’t mean it’s not one of the most useful and versatile functions …

The Excel DATE function combines separate year, month and day values and converts them into a date serial number formatted as a date. Excel DATE Function Syntax Syntax: =DATE(year, month, day) year The value of the year argument can include one to four digits from 1900 to 9999. month A positive or negative integer representing …

Often, we'll have a list of month names that we want to convert to a date, or even just convert month names to numbers. We can use the MONTH function to do this; see examples below: How it works: By concatenating a 1 to the text month name in column B we provide the MONTH …

If you want to use a date in a chart label, or you want to concatenate a date with some other text, then you'll want to convert dates to text first to ensure it displays correctly. We can use the TEXT function for this. The second argument of the text function allows us to specify …

In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells contributed to a result, like this: However, in financial modelling and reporting it’s often less obvious, like the example below: Let’s look at some ways we can easily identify which cells contribute to a result. …